Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2011
    Posts
    2

    Unanswered: Group by problem

    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

    [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?

  2. #2
    Join Date
    May 2008
    Posts
    277
    Remove the "id" column from your inner selects.

    Remove the GROUP BY clause from your outer select.

  3. #3
    Join Date
    Jan 2011
    Posts
    2
    Great Help!!! Just Great!!! Thanks a lot...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •