Results 1 to 7 of 7

Thread: UNION ALL sum

  1. #1

    UNION ALL sum

    Hallo,

    Ik wil een aantal query's middels een UNION ALL weergeven. Zie onderstaande code.
    Het resultaat is niet wat ik had gehoopt. In MSSQL worden getallen velden in een UNION ALL opgeteld. Kan ik dat in interbase ook bewerkstelligen? Nu worden alle regels met een unieke waarde weergegeven, ik zou deze graag opgeteld zien. Dus alle waardes van november en alle waardes van december opgeteld.


    Code:
    SELECT   cl.client_number,         
             cl.client_name,
             cl.client_additionalname,
             inv.invoice_year,
             sum(inve.inventry_declaredeuro) as november,
             0.00 as december
    FROM     invoiceentry inve 
             inner join invoice inv on inve.inventry_invoice_id1 = inv.invoice_id1 and inve.inventry_invoice_id2 = inv.invoice_id2 and inve.inventry_invoice_id3 = inv.invoice_id3 
             inner join timesheetentry tse on inve.inventry_tsentry_id1 = tse.tsentry_id1 and inve.inventry_tsentry_id2 = tse.tsentry_id2 and inve.inventry_tsentry_id3 = tse.tsentry_id3 
             inner join clienttimesheet cts on tse.tsentry_cts_id1 = cts.cts_id1 and tse.tsentry_cts_id2 = cts.cts_id2 and tse.tsentry_cts_id3 = cts.cts_id3 
             inner join officedeclarer od on cts.cts_offdeclarer_id1 = od.offdeclarer_id1 and cts.cts_offdeclarer_id2 = od.offdeclarer_id2 and cts.cts_offdeclarer_id3 = od.offdeclarer_id3 
             inner join users u on od.offdeclarer_user_id1 = u.user_id1 and od.offdeclarer_user_id1 = u.user_id1 and od.offdeclarer_user_id2 = u.user_id2 and od.offdeclarer_user_id3 = u.user_id3 
             inner join client cl on cts.cts_client_id1 = cl.client_id1 and cts.cts_client_id2 = cl.client_id2 and cts.cts_client_id3 = cl.client_id3 
             inner join activity act on tse.tsentry_activity_id1 = act.act_id1 and tse.tsentry_activity_id2 = act.act_id2 and tse.tsentry_activity_id3 = act.act_id3 
             inner join activitysubgroup actsgrp on act.act_activitysubgroup_id1 = actsgrp.actsubgroup_id1 and act.act_activitysubgroup_id2 = actsgrp.actsubgroup_id2 and act.act_activitysubgroup_id3 = actsgrp.actsubgroup_id3 
             inner join activitygroup actgrp on actsgrp.actsubgroup_actgroup_id1 = actgrp.actgroup_id1 and actsgrp.actsubgroup_actgroup_id2 = actgrp.actgroup_id2 and actsgrp.actsubgroup_actgroup_id3 = actgrp.actgroup_id3
    WHERE    
             cl.client_number = '0370410' and
             inv.invoice_year = 2016 and
             inv.invoice_faperiodenr = 12
    GROUP BY cl.client_number, cl.client_name, cl.client_additionalname, 
             inv.invoice_year, inv.invoice_faperiodenr, 
             act.act_code, act.act_description, tse.tsentry_fiscalyear, 
             actsgrp.actsubgroup_description, actgrp.actgroup_description
    
    
    UNION ALL
    
    
    SELECT   cl.client_number,
             cl.client_name,
             cl.client_additionalname,
             inv.invoice_year,
             0.00 as november,
             sum(inve.inventry_declaredeuro) as december
    FROM     invoiceentry inve 
             inner join invoice inv on inve.inventry_invoice_id1 = inv.invoice_id1 and inve.inventry_invoice_id2 = inv.invoice_id2 and inve.inventry_invoice_id3 = inv.invoice_id3 
             inner join timesheetentry tse on inve.inventry_tsentry_id1 = tse.tsentry_id1 and inve.inventry_tsentry_id2 = tse.tsentry_id2 and inve.inventry_tsentry_id3 = tse.tsentry_id3 
             inner join clienttimesheet cts on tse.tsentry_cts_id1 = cts.cts_id1 and tse.tsentry_cts_id2 = cts.cts_id2 and tse.tsentry_cts_id3 = cts.cts_id3 
             inner join officedeclarer od on cts.cts_offdeclarer_id1 = od.offdeclarer_id1 and cts.cts_offdeclarer_id2 = od.offdeclarer_id2 and cts.cts_offdeclarer_id3 = od.offdeclarer_id3 
             inner join users u on od.offdeclarer_user_id1 = u.user_id1 and od.offdeclarer_user_id1 = u.user_id1 and od.offdeclarer_user_id2 = u.user_id2 and od.offdeclarer_user_id3 = u.user_id3 
             inner join client cl on cts.cts_client_id1 = cl.client_id1 and cts.cts_client_id2 = cl.client_id2 and cts.cts_client_id3 = cl.client_id3 
             inner join activity act on tse.tsentry_activity_id1 = act.act_id1 and tse.tsentry_activity_id2 = act.act_id2 and tse.tsentry_activity_id3 = act.act_id3 
             inner join activitysubgroup actsgrp on act.act_activitysubgroup_id1 = actsgrp.actsubgroup_id1 and act.act_activitysubgroup_id2 = actsgrp.actsubgroup_id2 and act.act_activitysubgroup_id3 = actsgrp.actsubgroup_id3 
             inner join activitygroup actgrp on actsgrp.actsubgroup_actgroup_id1 = actgrp.actgroup_id1 and actsgrp.actsubgroup_actgroup_id2 = actgrp.actgroup_id2 and actsgrp.actsubgroup_actgroup_id3 = actgrp.actgroup_id3
    WHERE    
             cl.client_number = '0370410' and
             inv.invoice_year = 2016-1 and
             inv.invoice_faperiodenr = 1
    GROUP BY cl.client_number, cl.client_name, cl.client_additionalname, 
             inv.invoice_year, inv.invoice_faperiodenr, 
             act.act_code, act.act_description, tse.tsentry_fiscalyear, 
             actsgrp.actsubgroup_description, actgrp.actgroup_description
    Resultaat:

    CLIENT_NUMBER CLIENT_NAME CLIENT_ADDITIONALNAME INVOICE_YEAR NOVEMBER DECEMBER
    0370410 test 2.016 158,500 0,000
    0370410 test 2.016 108,000 0,000
    0370410 test 2.016 72,000 0,000
    0370410 test 2.016 1071,500 0,000
    0370410 test 2.016 23,500 0,000
    0370410 test 2.016 443,750 0,000
    0370410 test 2.016 36,000 0,000
    0370410 test 2.016 0,000 105,000
    0370410 test 2.016 0.000 292,500

    Verwacht resultaat:

    CLIENT_NUMBER CLIENT_NAME CLIENT_ADDITIONALNAME INVOICE_YEAR NOVEMBER DECEMBER
    0370410 test 2.016 1913,250 397,500

  2. #2
    Dacht dat het probleem in de group by zit. Als ik daar alles uithaal behalve de getoonde velden gaat het goed als ik echter act.act_code toevoeg gaat het weer fout hoewel deze wel hetzelfde is.
    Last edited by Pascal G++; 24-Feb-17 at 16:46.

  3. #3
    Werkt het wel als je iedere gedeelte van de union apart uitvoert? Of krijg je dan ook meerdere rijen?

  4. #4
    Je doet volgens mij veel te veel bij "group by".
    Je hebt de velden:
    Code:
    SELECT   cl.client_number,
             cl.client_name,
             cl.client_additionalname,
             inv.invoice_year,
             0.00 as november,
             sum(inve.inventry_declaredeuro) as december
    Dus de group by moet alleen over de eerste 5 velden.
    Dus:
    Code:
    group by 1,2,3,4,5
    of
    Code:
    group by cl.client_number, cl.client_name, cl.client_additionalname, inv.invoice_year, november

  5. #5
    Je doet volgens mij veel te veel bij "group by".
    Mag dat niet of is het gewoon niet "mooi".

  6. #6
    Het mag wel, maar je loopt de kans dat je een gegeven selecteert dat in verschillende waarden voorkomt voor de gekozen klant, met als gevolg dat je de klant twee keer in je resultaat krijgt, met twee subtotalen, terwijl je niet kan zien waar dat door komt.
    1+1=b

  7. #7
    Quote Originally Posted by luigi View Post
    Mag dat niet of is het gewoon niet "mooi".
    Dat is de reden dat je meer dan 1 record krijgt per select (voor elke kolom die je opneemt bij de GROUP BY, ontstaan een "groepen" = in dit geval record (maar omdat je die velden niet in de select toont, ziet het resultaat er 'fout' uit . Denk trouwens dat als je dat corrigeert je nog altijd 2 record krijgt. Die kun je weer 'samenvoegen' met (heb ook even ene sum om de 0 gezet, dan hoef ik deze kolom niet in de group by te noemen, en hij is toch constant (gewoon luiheid)
    (dit mag in Oracle en sqlite in ieder geval)

    Code:
    Select  client_number,         
             client_name,
             client_additionalname,
             invoice_year,
             sum(november) as november,
             sum(december) as december
    form (
    SELECT   cl.client_number,         
             cl.client_name,
             cl.client_additionalname,
             inv.invoice_year,
             sum(inve.inventry_declaredeuro) as november,
             sum(0.00) as december
    FROM     invoiceentry inve 
             inner join invoice inv on inve.inventry_invoice_id1 = inv.invoice_id1 and inve.inventry_invoice_id2 = inv.invoice_id2 and inve.inventry_invoice_id3 = inv.invoice_id3 
             inner join timesheetentry tse on inve.inventry_tsentry_id1 = tse.tsentry_id1 and inve.inventry_tsentry_id2 = tse.tsentry_id2 and inve.inventry_tsentry_id3 = tse.tsentry_id3 
             inner join clienttimesheet cts on tse.tsentry_cts_id1 = cts.cts_id1 and tse.tsentry_cts_id2 = cts.cts_id2 and tse.tsentry_cts_id3 = cts.cts_id3 
             inner join officedeclarer od on cts.cts_offdeclarer_id1 = od.offdeclarer_id1 and cts.cts_offdeclarer_id2 = od.offdeclarer_id2 and cts.cts_offdeclarer_id3 = od.offdeclarer_id3 
             inner join users u on od.offdeclarer_user_id1 = u.user_id1 and od.offdeclarer_user_id1 = u.user_id1 and od.offdeclarer_user_id2 = u.user_id2 and od.offdeclarer_user_id3 = u.user_id3 
             inner join client cl on cts.cts_client_id1 = cl.client_id1 and cts.cts_client_id2 = cl.client_id2 and cts.cts_client_id3 = cl.client_id3 
             inner join activity act on tse.tsentry_activity_id1 = act.act_id1 and tse.tsentry_activity_id2 = act.act_id2 and tse.tsentry_activity_id3 = act.act_id3 
             inner join activitysubgroup actsgrp on act.act_activitysubgroup_id1 = actsgrp.actsubgroup_id1 and act.act_activitysubgroup_id2 = actsgrp.actsubgroup_id2 and act.act_activitysubgroup_id3 = actsgrp.actsubgroup_id3 
             inner join activitygroup actgrp on actsgrp.actsubgroup_actgroup_id1 = actgrp.actgroup_id1 and actsgrp.actsubgroup_actgroup_id2 = actgrp.actgroup_id2 and actsgrp.actsubgroup_actgroup_id3 = actgrp.actgroup_id3
    WHERE    
             cl.client_number = '0370410' and
             inv.invoice_year = 2016 and
             inv.invoice_faperiodenr = 12
    GROUP BY cl.client_number, cl.client_name, cl.client_additionalname, 
             inv.invoice_year
    
    UNION ALL
    
    SELECT   cl.client_number,
             cl.client_name,
             cl.client_additionalname,
             inv.invoice_year,
             sum(0.00) as november,
             sum(inve.inventry_declaredeuro) as december
    FROM     invoiceentry inve 
             inner join invoice inv on inve.inventry_invoice_id1 = inv.invoice_id1 and inve.inventry_invoice_id2 = inv.invoice_id2 and inve.inventry_invoice_id3 = inv.invoice_id3 
             inner join timesheetentry tse on inve.inventry_tsentry_id1 = tse.tsentry_id1 and inve.inventry_tsentry_id2 = tse.tsentry_id2 and inve.inventry_tsentry_id3 = tse.tsentry_id3 
             inner join clienttimesheet cts on tse.tsentry_cts_id1 = cts.cts_id1 and tse.tsentry_cts_id2 = cts.cts_id2 and tse.tsentry_cts_id3 = cts.cts_id3 
             inner join officedeclarer od on cts.cts_offdeclarer_id1 = od.offdeclarer_id1 and cts.cts_offdeclarer_id2 = od.offdeclarer_id2 and cts.cts_offdeclarer_id3 = od.offdeclarer_id3 
             inner join users u on od.offdeclarer_user_id1 = u.user_id1 and od.offdeclarer_user_id1 = u.user_id1 and od.offdeclarer_user_id2 = u.user_id2 and od.offdeclarer_user_id3 = u.user_id3 
             inner join client cl on cts.cts_client_id1 = cl.client_id1 and cts.cts_client_id2 = cl.client_id2 and cts.cts_client_id3 = cl.client_id3 
             inner join activity act on tse.tsentry_activity_id1 = act.act_id1 and tse.tsentry_activity_id2 = act.act_id2 and tse.tsentry_activity_id3 = act.act_id3 
             inner join activitysubgroup actsgrp on act.act_activitysubgroup_id1 = actsgrp.actsubgroup_id1 and act.act_activitysubgroup_id2 = actsgrp.actsubgroup_id2 and act.act_activitysubgroup_id3 = actsgrp.actsubgroup_id3 
             inner join activitygroup actgrp on actsgrp.actsubgroup_actgroup_id1 = actgrp.actgroup_id1 and actsgrp.actsubgroup_actgroup_id2 = actgrp.actgroup_id2 and actsgrp.actsubgroup_actgroup_id3 = actgrp.actgroup_id3
    WHERE    
             cl.client_number = '0370410' and
             inv.invoice_year = 2016-1 and
             inv.invoice_faperiodenr = 1
    GROUP BY cl.client_number, cl.client_name, cl.client_additionalname, 
             inv.invoice_year
    
    )
    group by   client_number,         
             client_name,
             client_additionalname,
             invoice_year

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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