Group By Month and Customer in a single row

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Group By Month and Customer in a single row

Author

Topic

kanupathak
Starting Member

5 Posts

Posted - 2010-11-29 : 11:19:13

Hi folks,

My goal is to generate year end total sales by customer and month.
The result should look like:

Jan Feb March .....
==========================
Cust1
Cust2
Cust3
Cust4
.
.
.

The problem is when I am grouping by both customer and month, monthly sales for each customer will come in separate rows.
Jan Feb March .....
==========================
Cust1
Cust1
Cust2
Cust3
Cust3
.
.
.

Code I am using is:

SELECT 
T0.[CardCode] AS 'Customer Code', 

CASE
 WHEN DATENAME(month, T0.[DocDate]) = 'April'
 THEN (SELECT SUM(T1.[Price]*T1.[Quantity]))
END AS 'April ',

CASE
 WHEN DATENAME(month, T0.[DocDate]) = 'May'
 THEN (SELECT SUM(T1.[Price]*T1.[Quantity]))
END AS 'May ',

CASE
 WHEN DATENAME(month, T0.[DocDate]) = 'June'
 THEN (SELECT SUM(T1.[Price]*T1.[Quantity]))
END AS 'June ',

CASE
 WHEN DATENAME(month, T0.[DocDate]) = 'July'
 THEN (SELECT SUM(T1.[Price]*T1.[Quantity]))
END AS 'July ',

CASE
 WHEN DATENAME(month, T0.[DocDate]) = 'August'
 THEN (SELECT SUM(T1.[Price]*T1.[Quantity]))
END AS 'August ',

CASE
 WHEN DATENAME(month, T0.[DocDate]) = 'September'
 THEN (SELECT SUM(T1.[Price]*T1.[Quantity]))
END AS 'September ',

CASE
 WHEN DATENAME(month, T0.[DocDate]) = 'October'
 THEN (SELECT SUM(T1.[Price]*T1.[Quantity]))
END AS 'October ',

CASE
 WHEN DATENAME(month, T0.[DocDate]) = 'November'
 THEN (SELECT SUM(T1.[Price]*T1.[Quantity]))
END AS 'November ',

CASE
 WHEN DATENAME(month, T0.[DocDate]) = 'December'
 THEN (SELECT SUM(T1.[Price]*T1.[Quantity]))
END AS 'December ',

CASE
 WHEN DATENAME(month, T0.[DocDate]) = 'January'
 THEN (SELECT SUM(T1.[Price]*T1.[Quantity]))
END AS 'January ',

CASE
 WHEN DATENAME(month, T0.[DocDate]) = 'February'
 THEN (SELECT SUM(T1.[Price]*T1.[Quantity]))
END AS 'February ',

CASE
 WHEN DATENAME(month, T0.[DocDate]) = 'March'
 THEN (SELECT SUM(T1.[Price]*T1.[Quantity]))
END AS 'March '

FROM ORDR T0  
INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry 
INNER JOIN [dbo].[OITM]  T2 ON T1.ItemCode = T2.ItemCode 



GROUP BY 
DATENAME(month, T0.[DocDate]), T0.[CardCode]

Please Help.
Kanu

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-29 : 11:28:02

SELECT
T0.[CardCode] AS 'Customer Code',
sum(CASE
WHEN DATENAME(month, T0.[DocDate]) = 'April'
THEN T1.[Price]*T1.[Quantity] else 0
END AS 'April ',
...
group by T0.[CardCode]

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

kanupathak
Starting Member

5 Posts

Posted - 2010-11-29 : 11:44:23

quote:
Originally posted by nigelrivett

SELECT
T0.[CardCode] AS 'Customer Code',
sum(CASE
WHEN DATENAME(month, T0.[DocDate]) = 'April'
THEN T1.[Price]*T1.[Quantity] else 0
END AS 'April ',
...
group by T0.[CardCode]

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

kanupathak
Starting Member

5 Posts

Posted - 2010-11-29 : 11:51:06

Thank you for the reply. I modified the code but its showing error: Incorrect Syntax near 'April'
Sorry for the incomplete last reply. Wanted to delete but cannot find the delete tab.

Regards,
Kanu

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-29 : 12:07:02

Sory - missed a bracket - also better not to use quotes as delimitters for identifiers

SELECT
T0.[CardCode] AS 'Customer Code',
sum(CASE
WHEN DATENAME(month, T0.[DocDate]) = 'April'
THEN T1.[Price]*T1.[Quantity] else 0
END) AS [April],
...
FROM ORDR T0
INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN [dbo].[OITM] T2 ON T1.ItemCode = T2.ItemCode
group by T0.[CardCode]

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

kanupathak
Starting Member

5 Posts

Posted - 2010-11-30 : 05:39:15

quote:
Originally posted by nigelrivett

Sory - missed a bracket - also better not to use quotes as delimitters for identifiers

SELECT
T0.[CardCode] AS 'Customer Code',
sum(CASE
WHEN DATENAME(month, T0.[DocDate]) = 'April'
THEN T1.[Price]*T1.[Quantity] else 0
END) AS [April],
...
FROM ORDR T0
INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN [dbo].[OITM] T2 ON T1.ItemCode = T2.ItemCode
group by T0.[CardCode]

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Thanks a lot Nigel. It is working fabulously.

But, I believe I can not use the same logic for:

CASE
 WHEN DATENAME(month, T0.[DocDate]) = 'April'
 THEN (SELECT SUM(T1.[Price]*T1.[Quantity])/(SUM(T2.[BWeight1]*T1.[Quantity])/1000))
END AS 'April (Sales per Tonne)',

Sorry for coming back like a kid but couldn't think of anything.

Regards,
Kanu

kanupathak
Starting Member

5 Posts

Posted - 2010-11-30 : 07:24:59

I am using this code as well:

SELECT *
FROM (
  SELECT
    T0.[CardCode],
    T1.[Price],
    T1.[Quantity],
    T2.[BWeight1],
    
    CASE MONTH(T0.[DocDate]) 
      WHEN 1 THEN 'January'
      WHEN 2 THEN 'February'
      WHEN 3 THEN 'March'
      WHEN 4 THEN 'April'
      WHEN 5 THEN 'May'
      WHEN 6 THEN 'June'
      WHEN 7 THEN 'July'
      WHEN 8 THEN 'August'
      WHEN 9 THEN 'September'
      WHEN 10 THEN 'October'
      WHEN 11 THEN 'November'
      WHEN 12 THEN 'December'
    END as [Month]
    
  FROM ORDR T0 
       INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry 
       INNER JOIN [dbo].[OITM] T2 ON T1.ItemCode = T2.ItemCode 
     ) As TableDate
PIVOT (
  SUM(T1.[Price]*T1.[Quantity])/(SUM(T2.[BWeight1]*T1.[Quantity])/1000)
  FOR [Month] IN (
    [January],[February],[March],[April],
    [May],[June],[July],[August],
    [September],[October],[November],[December])
      ) As PivotTable

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources