How to sum up the row? please see the code below.

[raynoel]

I wanted to sum up the total in table expenses "debit" row.
currently it's connected with 3 tables for the debit. Hope you can help me here.
------no. one query--------
SELECT a.debit, a.credit, a.action_date,d.compname, b.labfee, b.payee FROM expenses a
INNER JOIN memberlaboratory b ON a.memberlaboratoryidfk = b.memberlaboratoryid
INNER JOIN enrollment c ON b.enrollmentidfk = c.enrollmentid
INNER JOIN company d ON c.companyidfk = d.companyid
WHERE d.companyid = 77 AND c.enrollmentstatusidfk = 0

------no. two query--------
SELECT a.debit, a.credit, a.action_date,d.compname FROM expenses a
INNER JOIN patient e ON a.patientidfk = e.patientid
INNER JOIN hospital b ON e.hospitalidfk = b.hospitalid
INNER JOIN enrollment c ON e.enrollmentidfk = c.enrollmentid
INNER JOIN company d ON c.companyidfk = d.companyid
WHERE d.companyid = 77 AND c.enrollmentstatusidfk = 0;

------no. three query--------
SELECT SUM(debit) FROM expenses
INNER JOIN memberlaboratory b ON a.memberlaboratoryidfk = b.memberlaboratoryid
INNER JOIN consultation f ON a.consultationidfk = f.consultationid
INNER JOIN patient e ON a.patientidfk = e.patientid
INNER JOIN hospital g ON e.hospitalidfk = g.hospitalid
INNER JOIN enrollment c ON e.enrollmentidfk = c.enrollmentid
WHERE c.companyidfk = 77 AND c.enrollmentstatusidfk = 0


All three queries are comming from one company only but the value for row "debit" in table expenses are differents. HOw can I incorporate those to sum up the total? Pls help..tnx.

 

[feherke]

Hi

I am not sure I understand you correctly. So you have the first and second selects and you want to get a grand total by [tt]sum()[/tt]-ing the debits from both. And your try is the third select.

That could not work that way. Those [tt]inner join[/tt]s are excluding some records and puting them together that way I would predict a sum of zero.

[b]select[/b]
sum(debit)

[b]from[/b] (

  [b]select[/b]
  a.debit

  [b]from[/b] expenses a
  [b]inner join[/b] memberlaboratory b [b]on[/b] a.memberlaboratoryidfk = b.memberlaboratoryid
  [b]inner join[/b] enrollment c [b]on[/b] b.enrollmentidfk = c.enrollmentid
  [b]inner join[/b] company d [b]on[/b] c.companyidfk = d.companyid

  [b]where[/b] d.companyid = 77 [b]and[/b] c.enrollmentstatusidfk = 0

  [b]union all[/b]

  [b]select[/b]
  a.debit

  [b]from[/b] expenses a
  [b]inner join[/b] patient e [b]on[/b] a.patientidfk = e.patientid
  [b]inner join[/b] hospital b [b]on[/b] e.hospitalidfk = b.hospitalid
  [b]inner join[/b] enrollment c [b]on[/b] e.enrollmentidfk = c.enrollmentid
  [b]inner join[/b] company d [b]on[/b] c.companyidfk = d.companyid

  [b]where[/b] d.companyid = 77 [b]and[/b] c.enrollmentstatusidfk = 0
)

For better performance [tt]sum()[/tt] the subselects to pass less data to the outer select.

[b]select[/b]
sum([red]sum[/red])

[b]from[/b] (

  [b]select[/b]
  [red]sum([/red]a.debit[red])[/red]

  [gray]-- ...[/gray]

  [b]union all[/b]

  [b]select[/b]
  [red]sum([/red]a.debit[red])[/red]

  [gray]-- ...[/gray]
)

Feherke.

http://rootshell.be/~feherke/

 

[raynoel]

When I try to run the two scripts above i got an error
******
ERROR: subquery in FROM must have an alias
HINT: For example, FROM (SELECT ...) [AS] foo.
******
I got new QUERY below which will display the sum....

QUERY 1
SELECT SUM(a.debit), SUM(a.credit), d.compname FROM expenses a
INNER JOIN consultation b ON a.consultationidfk = b.consultationid
INNER JOIN enrollment c ON b.enrollmentidfk = c.enrollmentid
INNER JOIN company d ON c.companyidfk = d.companyid
WHERE d.companyid = 77 AND c.enrollmentstatusidfk = 0
GROUP BY d.compname;

QUERY 2
SELECT sum(a.debit), sum(a.credit), d.compname FROM expenses a
INNER JOIN memberlaboratory b ON a.memberlaboratoryidfk = b.memberlaboratoryid
INNER JOIN enrollment c ON b.enrollmentidfk = c.enrollmentid
INNER JOIN company d ON c.companyidfk = d.companyid
WHERE d.companyid = 77 AND c.enrollmentstatusidfk = 0
GROUP BY d.compname ;

QUERY3
SELECT SUM(a.debit), SUM(a.credit), d.compname FROM expenses a
INNER JOIN patient e ON a.patientidfk = e.patientid
INNER JOIN hospital b ON e.hospitalidfk = b.hospitalid
INNER JOIN enrollment c ON e.enrollmentidfk = c.enrollmentid
INNER JOIN company d ON c.companyidfk = d.companyid
WHERE d.companyid = 77 AND c.enrollmentstatusidfk = 0
GROUP BY d.compname;

How can I end up computing them all, I mean all the sum into 1?

 

[feherke]

Hi

ERROR: subquery in FROM must have an alias
HINT: For example, FROM (SELECT ...) [AS] foo.

Oops. Sorry. Put an alias there.

[b]select[/b]
sum(debit)

[b]from[/b] (

  [gray]-- ...[/gray]

) [red]foo[/red]

How can I end up computing them all, I mean all the sum into 1?

I am still sure that quory3 will not work and you must use subselect. For your new selects you will need some aliases for the fields too.

[b]select[/b]
sum(debit) as debit,sum(credit) as credit,compname

[b]from[/b] (

  [b]select[/b]
  sum(a.debit) as debit,sum(a.credit) as credit,d.compname

  [b]from[/b] expenses a
  [b]inner join[/b] consultation b [b]on[/b] a.consultationidfk = b.consultationid
  [b]inner join[/b] enrollment c [b]on[/b] b.enrollmentidfk = c.enrollmentid
  [b]inner join[/b] company d [b]on[/b] c.companyidfk = d.companyid

  [b]where[/b] d.companyid = 77 [b]and[/b] c.enrollmentstatusidfk = 0

  [b]group by[/b] d.compname

  [b]union all[/b]

  [b]select[/b]
  sum(a.debit),sum(a.credit),d.compname

  [b]from[/b] expenses a
  [b]inner join[/b] memberlaboratory b [b]on[/b] a.memberlaboratoryidfk = b.memberlaboratoryid
  [b]inner join[/b] enrollment c [b]on[/b] b.enrollmentidfk = c.enrollmentid
  [b]inner join[/b] company d [b]on[/b] c.companyidfk = d.companyid

  [b]where[/b] d.companyid = 77 [b]and[/b] c.enrollmentstatusidfk = 0

  [b]group by[/b] d.compname

) foo

Feherke.

http://rootshell.be/~feherke/

 

[raynoel]

I check my no. 3 query, it has the same code of the two queries, hmmm.... what you mean
*****
For your new selects you will need some aliases for the fields too.
****
by the way I tried to copy your code another error received.
****

ERROR: column "foo.compname" must appear in the GROUP BY clause or be used in an aggregate function
****
sorry to bother, i'm new to postgre, still going for one month... tnx for your patient...

 

[feherke]

Hi

I check my no. 3 query, it has the same code of the two queries, hmmm.... what you mean

I hope this examples will expalin better then me.

[gray]-- the three tables used in  this example : t_one, t_two, t_three[/gray]
[blue]SQL>[/blue] [b]select[/b] * [b]from[/b] t_one;
NAME
----------
A
B
C
D
E

[blue]SQL>[/blue] [b]select[/b] * [b]from[/b] t_two;
NAME            VALUE
---------- ----------
A                   1
B                   2
C                   3

[blue]SQL>[/blue] [b]select[/b] * [b]from[/b] t_three;
NAME            VALUE
---------- ----------
C                   3
D                   4

[gray]-- sums from t_one joined witht_two[/gray]
[blue]SQL>[/blue] [b]select[/b] t1.name,sum(t2.value) [b]from[/b] t_one t1 [b]inner join[/b] t_two t2 [b]on[/b] t2.name=t1.name [b]group by[/b] t1.name;
NAME       SUM(T2.VALUE)
---------- -------------
A                      1
B                      2
C                      3

[gray]-- sums from t_one joined with t_theree[/gray]
[blue]SQL>[/blue] [b]select[/b] t1.name,sum(t3.value) [b]from[/b] t_one t1 [b]inner join[/b] t_three t3 [b]on[/b] t3.name=t1.name [b]group by[/b] t1.name;
NAME       SUM(T3.VALUE)
---------- -------------
C                      3
D                      4

[gray]-- your way to combine the two joins[/gray]
[blue]SQL>[/blue] [b]select[/b] t1.name,sum(t2.value+t3.value) [b]from[/b] t_one t1 [b]inner join[/b] t_two t2 [b]on[/b] t2.name=t1.name [b]inner join[/b] t_two t3 [b]on[/b] t3.name=t1.name [b]group by[/b] t1.name;
NAME       SUM(T2.VALUE+T3.VALUE)
---------- ----------------------
A                               2
B                               4
C                               6

[gray]-- what I proposed with subselects[/gray]
[blue]SQL>[/blue] [b]select[/b] name,sum(subtotal) [b]from[/b] ( [b]select[/b] t1.name,sum(t2.value) [b]as[/b] subtotal [b]from[/b] t_one t1 [b]inner join[/b] t_two t2 [b]on[/b] t2.name=t1.name [b]group by[/b] t1.name [b]union all select[/b] t1.name,sum(t3.value) [b]from[/b] t_one t1 [b]inner join[/b] t_three t3 [b]on[/b] t3.name=t1.name [b]group by[/b] t1.name ) foo [b]group by[/b] name;
NAME       SUM(SUBTOTAL)
---------- -------------
A                      1
B                      2
C                      6
D                      4

Please ignore the fact that the above example was done in Oracle. Your problem is SQL concept related, not strictly PostgreSQL.

Please chect the calculations manually. Of course, it is possible that you need the results of your query. But I do not really belive.

Sorry again. I am quite forgetful today.

[b]select[/b]
sum(debit) as debit,sum(credit) as credit,compname

[b]from[/b] (

  [gray]-- ...[/gray]

) foo

[red][b]group by[/b] compname[/red]

Feherke.

http://rootshell.be/~feherke/

 

[raynoel]

thanks so much Feherke.... i'll try work this one today... hopefully it will going smooth... with your help... by the
way I check your site... hmm.. you love hand codes really... so goodbye ide softwre...??..hehe...

 

[raynoel]

hi Feherke ,

I already solved the issue.

the code below.....

select
sum(debit)-sum(credit) AS total ,compname

from (

select
sum(a.debit) as debit,sum(a.credit) as credit,d.compname

from expenses a
inner join consultation b on a.consultationidfk = b.consultationid
inner join enrollment c on b.enrollmentidfk = c.enrollmentid
inner join company d on c.companyidfk = d.companyid

where d.companyid = 67 and c.enrollmentstatusidfk = 0

group by d.compname

union all

select
sum(a.debit),sum(a.credit),d.compname

from expenses a
inner join memberlaboratory b on a.memberlaboratoryidfk = b.memberlaboratoryid
inner join enrollment c on b.enrollmentidfk = c.enrollmentid
inner join company d on c.companyidfk = d.companyid

where d.companyid = 67 and c.enrollmentstatusidfk = 0

group by d.compname
union all

SELECT SUM(a.debit), SUM(a.credit), d.compname FROM expenses a
INNER JOIN patient e ON a.patientidfk = e.patientid
INNER JOIN hospital b ON e.hospitalidfk = b.hospitalid
INNER JOIN enrollment c ON e.enrollmentidfk = c.enrollmentid
INNER JOIN company d ON c.companyidfk = d.companyid
WHERE d.companyid = 67 AND c.enrollmentstatusidfk = 0
GROUP BY d.compname

) foo group by compname

I also got the total for debit - credit ...

thanks!