#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2011
    Posts
    6
    Rep Power
    0

    SQL Summary Query - Help Needed


    Hi,

    I have a database with the following values..

    Code:
    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.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,166
    Rep Power
    4274
    Originally Posted by Dapa
    and then a sum of the quantities for P only when status P & R exist.
    is there a GROUP BY involved in this query?

    or are you simply looking for P + R whenever there's at least one of each in the entire table?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2011
    Posts
    6
    Rep Power
    0
    Originally Posted by r937
    is there a GROUP BY involved in this query?

    or are you simply looking for P + R whenever there's at least one of each in the entire table?
    I'd like it to eventually group by date.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,166
    Rep Power
    4274
    Code:
    SELECT "date"
         , 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'
                     AND EXISTS
                         ( SELECT 'R'
                             FROM daTable
                            WHERE "date" = t."date"
                              AND status = 'R' )   
                    THEN qty ELSE NULL END) AS status_p_r
      FROM daTable AS t
    GROUP
        BY "date"
    DATE is a reserved word in many database systems, and needs to be escaped

    you did not mention which database system you're using, so i chose to escape the column name using standard sql doublequote delimiters
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo