June 16th, 2012, 04:55 AM
SQL Summary Query - Help Needed
I have a database with the following values..
DATE | TIME | STATUS |REF | UID | QTY
23-05 | 19:31:45 | P |A |71709 | 50
23-05 | 15:58:19 | P |B |71724 | 24
23-05 | 06:30:13 | R |B |71724 | 24
23-05 | 06:30:37 | R |B |71725 | 14
23-05 | 06:34:58 | R |C |71726 | 24
23-05 | 15:48:09 | P |D |71727 | 48
23-05 | 06:35:15 | R |D |71727 | 48
I want to create a summary to sum up the values of everything with a status of P, everything with a status of R and then a sum of the quantities for P only when status P & R exist.
For example the result of the query to the above data would return..
STATUS_R = 110 (24+14+24+48)
STATUS_P = 122 (50+24+48)
STATUS_R&P = 72 (24+48)
I've managed to do the status_r and status_p using case.
sum(case when status = 'R' then qty else 0 end)
sum(case when status = 'P' then qty else 0 end)
However I'm struggling with the status_r&p
Any help would be appreciated.
June 16th, 2012, 05:13 AM
is there a GROUP BY involved in this query?
Originally Posted by Dapa
or are you simply looking for P + R whenever there's at least one of each in the entire table?
June 16th, 2012, 05:16 AM
I'd like it to eventually group by date.
Originally Posted by r937
June 20th, 2012, 06:47 AM
DATE is a reserved word in many database systems, and needs to be escaped
, SUM(CASE WHEN status='P'
THEN qty ELSE NULL END) AS status_p
, SUM(CASE WHEN status='R'
THEN qty ELSE NULL END) AS status_r
, SUM(CASE WHEN status = 'P'
( SELECT 'R'
WHERE "date" = t."date"
AND status = 'R' )
THEN qty ELSE NULL END) AS status_p_r
FROM daTable AS t
you did not mention which database system you're using, so i chose to escape the column name using standard sql doublequote delimiters