Group by problem

mrtblt · #1 (**permalink**) 01-06-11, 00:19

I am trying to find out client transactions sums with the following query

Code:

SELECT c.id,c.vergid,c.vergino,c.bank,c.iban,c.address_inv,c.cl_name, c.tip, c.sehir,c.ilce,c.contact_person,c.mobil1,c.aciklama,sumfa, sumfb,sumcsa, sumcsb,sumcfa, sumcfb from clients as c 
		
left join ( select id,client_id, sum(grand_sum*(100-disc_prcnt)/100) as sumfa from inv_header where inv_type in (1,2,3,4,5,6,7,8,9,10) group by client_id) as iha
on c.id=iha.client_id
left join ( select id,client_id, sum(grand_sum*(100-disc_prcnt)/100) as sumfb from inv_header where inv_type in (11,12,13,14,15,16,17,18,19,20) group by client_id) as ihb
on c.id=ihb.client_id
	
left join ( select id,cl_id, sum(miktar) as sumcsa from ceksenet where tip in (1,2,3,4,5,6,7,8,9,10) group by cl_id) as csa
on c.id=csa.cl_id
left join ( select id,cl_id, sum(miktar) as sumcsb from ceksenet where tip in (11,12,13,14,15,16,17,18,19,20) group by cl_id) as csb
on c.id=csb.cl_id
		
left join ( select id,cl_id, sum(miktar) as sumcfa from cfis where tip='TAHSILAT' group by cl_id) as cfa
on c.id=cfa.cl_id
left join ( select id,cl_id, sum(miktar) as sumcfb from cfis where tip='ODEME' group by cl_id) as cfb
on c.id=cfb.cl_id
	
group by c.id, iha.id,ihb.id, csa.id,csb.id, cfa.id,cfb.id ORDER BY cl_name asc

This query is supposed to scan invoices, cheques and other related tables for each client and calculate the sums.

For calculations from the respective tables i want to create the following fields in the query also

Code:

sumfa, sumfb,sumcsa, sumcsb,sumcfa, sumcfb

But postgresql enforces me to add all the fields included in the select in Group By with the following error code

Quote:

[Err] ERROR: column "c.vergid" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT c.id,c.vergid,c.vergino,c.bank,c.iban,c.address_in v,c...

When i include all the fields which i indicate in select then the query creates 19000 and so rows. Whereas i have only 68 clients in my clients table.

As far as I understand it creates a Cartesian result which i don't want.

What should i do to get what i want. I mean, transaction sums for each client from the respective tables?