CODEWITHSUNDEEP
sqlsql-serversum
Miguel Terlaak
Miguel Terlaak

Reputation: 175

Query over multiple tables including SUM

I am looking for the correct syntax

I can set up different queries but I am looking for one query

Tables:

T1
Country   Metal    Date       Pnum
Austria   Gold     20-Dec-18  P31182
Austria   Gold     20-Dec-18  P31181
Belgium   Grass    6-Dec-17   P30703
Italy     Gold     16-Nov-17  P30702
Zimbabwe  Copper   8-Jul-17   P29999

T2
Pnum    T  Week     Type  Color Descr               Status    Buy
P31182  2  2019-23  Apple Black Description one     Ready     200
P31181  1  2019-23  Apple Black Description two     Ready     150
P30703  2  2019-28  Apple Black Description four    Waiting   120

T3
Work    Hr    Pr    Pnum
AFNA    4     3.50  P31182
CONS    3     1.25  P31182
TREX    2.5   3.00  P31182
AFNA    3     3.50  P31181
CONS    3     1.25  P31181
TREX    2.5   3.00  P31181
AFNA    4     3.50  P30703
MEET    5     5.00  P30703
TREX    2.5   3.00  P30703

T4
Used      Pnum
3010.50   P31182
500.00    P31182
2254.47   P31181
555.55    P30703
155.25    P30703

Desired result: (minus the calculation in brackets, that's for clarity)

Country Date      Week    Type  Color   Metal Pnum    Descr            Status   Hr*T    Hr*T*Pr      Used*T      Buy*T      Work
Austria 20-Dec-18 2019-23 Apple Black   Gold  P31182  Description one  Ready    19      50.50        7021        400                
Austria                                                                         (4*2)   (4*2*3.50)   (3010.50*2) (200*2)    AFNA
Austria                                                                         (3*2)   (3*2*1.25)   (500*2)                CONS
Austria                                                                         (2.5*2) (2.5*2*3)                           TREX
Austria 20-Dec-18 2019-23 Apple Black   Gold  P31181  Description two  Ready    8.5     25.50        2254.47     150                
Austria                                                                         (2.5*1) (2.5*1*3.00) (2254.47*1) (150*1)    AFNA
Austria                                                                         (3*1)   (3*1*3.50)                          CONS
Austria                                                                         (2.5*1) (2.5*1*3.00)                        TREX
Belgium  6-Dec-17 2019-28 Apple Black   Grass P30703  Description four Waiting  23      93.00        1421.60     240                
Belgium                                                                         (4*2)   (4*2*3.50)   (555.55*2)  (120*2)    AFNA
Belgium                                                                         (5*2)   (5*2*5.00)   (155.25)               MEET
Belgium                                                                         (2.5*2) (2.5*2*3)                           TREX

If I do something like this I get wrong totals under the SUM columns

SELECT Country, Date, Week, Type, Color, Metal, Pnum, Desc, Status, SUM(Hr*T), SUM(Hr*T*Pr), (Used*T), (Buy*T), Work
FROM T2
INNER JOIN T1 table1 on table1.Pnum = table2.Pnum
INNER JOIN T3 table3 on table3.Pnum = table2.Pnum
INNER JOIN T4 table4 on table4.Pnum = table2.Pnum
GROUP BY Pnum

EDIT Sorry, I forgot to include a Pnum in the first table. I will add it and will play around with the answers. Table T1 is not very important. It only needs some info from there depending on the Pnum in T2

Upvotes: 0

Views: 59

Answers (1)

APH
APH

Reputation: 4154

Try something like this (apologies for the vague references, but it seems that your original example is already somewhat simplified from reality):

; with CTE As 
     (Select Country, Date, Week, Pnum.... -- the rest of your columns here
     , sum(HR*T),... -- the rest of your aggregations here
     , '' as Work
     From T1
     Inner Join T2... -- the already working joins you have here
     Group by Country, Date, Week, Pnum... -- repeat the columns from the first line here

     union all

     Select distinct '', '', '', Pnum... --Add a '' here for each column that you want to be blank, otherwise pull the column name
     , Work -- retain this column
     From T3)

select * from CTE
order by Pnum, case when Work = '' then 1 else 2 end -- to sort the results the way you have them

Basically, you are creating your aggregate rows and your "work" rows separately, then combining them into one table and ordering the result to "group" them together.

Upvotes: 1

Related Questions