Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2005
    Posts
    16

    Unanswered: Using a CASE Statement within a Select Query

    Hi folks,

    Hope you are all well.

    I am using a CASE statement within a SELECT query to sum up values for different customers.

    Code:
    SELECT  CR_CUST.Customer_Code,
    	'General_01' = CASE WHEN CR_PROD.Part_Class_Code = '01' THEN SUM(CR_INVOICE.Line_Value) ELSE 0 END,
    	'General_07' = CASE WHEN CR_PROD.Part_Class_Code = '07' THEN SUM(CR_INVOICE.Line_Value) ELSE 0 END,
    	'General_08' = CASE WHEN CR_PROD.Part_Class_Code = '08' THEN SUM(CR_INVOICE.Line_Value) ELSE 0 END
    FROM    CR_CUST 
    	INNER JOIN CR_INVOICE ON CR_CUST.Customer_Code = CR_INVOICE.Customer_Code 
    	INNER JOIN CR_PROD ON CR_INVOICE.Product_Code = CR_PROD.Product_Code
    WHERE    (CR_PROD.Part_Class_Code = 1 OR 
     CR_PROD.Part_Class_Code = 7 OR 
    	 CR_PROD.Part_Class_Code = 8)
    GROUP BY CR_CUST.Customer_Code,
    	CR_PROD.Part_Class_Code
    The above query produces the following results...

    Code:
    Customer_Code General_01       General_07       General_08        
    ------------- ---------------- ---------------- ---------------- 
    02210         10074.30         .00              .00              
    02347         7606.49          .00              .00              
    02210         .00              12618.42         .00              
    02347         .00              13131.63         .00              
    02210         .00              .00              4505.44          
    02347         .00              .00              5018.03
    My question is this - is it possible to expand my SQL Query into a Sub Query so that each customers data appears on the same line of the results?, like so...

    Code:
    Customer_Code General_01       General_07       General_08        
    ------------- ---------------- ---------------- ---------------- 
    02210         10074.30         12618.42          4505.44              
    02347         7606.49          13131.63          5018.03
    I can achieve this by writing my results into a temporary table and extracting the data with the following SQL Query, but I just thought it would be really cool if I could do it in one SQL Statement without using a temporary table.

    Code:
    SELECT  Customer_Code,
    	SUM(General_01), 
    	SUM(General_07),
    	SUM(General_08)
    FROM #MyTempTable
    GROUP BY Customer_Code
    Thanks in advance,
    Kev

  2. #2
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    Change this
    GROUP BY CR_CUST.Customer_Code,
    CR_PROD.Part_Class_Code

    to this

    GROUP BY CR_CUST.Customer_Code

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, put your CASEs inside the SUMs
    Code:
    select CR_CUST.Customer_Code
         , sum(case when CR_PROD.Part_Class_Code = 1
                    then CR_INVOICE.Line_Value
                    else 0 end)          as General_01
         , sum(case when CR_PROD.Part_Class_Code = 7
                    then CR_INVOICE.Line_Value
                    else 0 end)          as General_07
         , sum(case when CR_PROD.Part_Class_Code = 8
                    then CR_INVOICE.Line_Value
                    else 0 end)          as General_08
      from CR_CUST 
    inner 
      join CR_INVOICE 
        on CR_INVOICE.Customer_Code = CR_CUST.Customer_Code
    inner 
      join CR_PROD 
        on CR_PROD.Product_Code = CR_INVOICE.Product_Code
     where CR_PROD.Part_Class_Code in ( 1, 7, 8 ) 
    group 
        by CR_CUST.Customer_Code
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    Good call missed that

  5. #5
    Join Date
    Nov 2005
    Posts
    16
    Perfect - that works a treat!

    Thanks for your help guys.

Posting Permissions

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