pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedselecting problems
2+ messages / 2 participants
[nested] [flat]
* selecting problems
@ 2003-10-31 19:19 Shaun Watts <SWatts@computer-systems.com>
0 siblings, 1 reply; 2+ messages in thread
From: Shaun Watts @ 2003-10-31 19:19 UTC (permalink / raw)
To: pgsql-sql
What I am trying to do is select all the rows out of the categories
table (see below) and select the sum of j_amount out of the judgment
table. Right now it is only returning the categories that are in the
judgment table. I want it to return those fields with the amounts out
of the judgment table, but also the all the other categories from the
categories table with 0 as there amount. Does anyone have a
suggestion.
select ca_code,ca_desc,sum(j_amount) as sum_amt,ca_dis_cycle
from categories LEFT OUTER JOIN judgment ON
(j_category=ca_code)
where j_case_no='45698'
and j_party_no=1
group by ca_code,ca_desc,ca_dis_cycle
Thanks,
Shaun
---------------------------------------------------------------------
Shaun, <mailto:swatts@computer-systems.com>
Phone 1-317-913-4160 Fax 1-317-913-4175
CSI - Computer Systems, Inc. <http://www.computer-systems.com/;
"Dictionary is the only place that success comes before work. Hard work is the price we
must pay for success. I think you can accomplish anything if you're willing to pay the price."
Vince Lombardi
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: selecting problems
@ 2003-10-31 19:53 Tomasz Myrta <jasiek@klaster.net>
parent: Shaun Watts <SWatts@computer-systems.com>
0 siblings, 0 replies; 2+ messages in thread
From: Tomasz Myrta @ 2003-10-31 19:53 UTC (permalink / raw)
To: Shaun Watts <SWatts@computer-systems.com>; +Cc: pgsql-sql
Dnia 2003-10-31 20:19, Użytkownik Shaun Watts napisał:
> What I am trying to do is select all the rows out of the categories
> table (see below) and select the sum of j_amount out of the judgment
> table. Right now it is only returning the categories that are in the
> judgment table. I want it to return those fields with the amounts out
> of the judgment table, but also the all the other categories from the
> categories table with 0 as there amount. Does anyone have a
> suggestion.
>
> select ca_code,ca_desc,sum(j_amount) as sum_amt,ca_dis_cycle
> from categories LEFT OUTER JOIN judgment ON
> (j_category=ca_code)
> where j_case_no='45698'
> and j_party_no=1
> group by ca_code,ca_desc,ca_dis_cycle
>
> Thanks,
> Shaun
This left join won't work. Where clause changes your left join into
inner one.
select ca_code,ca_desc,sum(j_amount) as sum_amt,ca_dis_cycle
from categories LEFT OUTER JOIN judgment ON
(j_category=ca_code and j_case_no='45698' and j_party_no=1)
group by ca_code,ca_desc,ca_dis_cycle
or
select ca_code,ca_desc,sum(j_amount) as sum_amt,ca_dis_cycle
from categories LEFT OUTER JOIN judgment ON (j_category=ca_code)
where (j_case_no='45698' and j_party_no=1) or j_category is null
group by ca_code,ca_desc,ca_dis_cycle
Probably you also need to change your sum(j_amount) into:
sum(case when j_amount is null then 0 else j_amount end)
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2003-10-31 19:53 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-10-31 19:19 selecting problems Shaun Watts <SWatts@computer-systems.com>
2003-10-31 19:53 ` Tomasz Myrta <jasiek@klaster.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox