I have a database table with a bunch of transactions. Each transaction has a company and date associated with it. I want to generate a table which has 12 columns, one for each month, and a row for every company. I want each entry to be the number of transactions in the column month with the row company. Also a 13th column with the total for the year for the company, and a final row with the total for the column month across all companies.
Simple little spreadsheet, but I can't figure out how to get there. I think I need to GROUP BY the companies. GROUP BY Company and SELECT COUNT(Transaction number) will give me one column with all the transactions of the company ever. I really need a count function which counts only those matching some month.
Can you help me?
I'm using MS SQL 2000.
thanks in advance
aniskhan
October 17th, 2006, 01:29 AM
cross-tab query tried it on Northwind, hope u get it
SELECT CustomerID,
Count(CASE MONTH(OrderDate) WHEN 1 THEN OrderID END) AS Jan,
Count(CASE MONTH(OrderDate) WHEN 2 THEN OrderID END) AS Feb,
Count(CASE MONTH(OrderDate) WHEN 3 THEN OrderID END) AS Mar,
Count(CASE MONTH(OrderDate) WHEN 4 THEN OrderID END) AS Apr,
Count(CASE MONTH(OrderDate) WHEN 5 THEN OrderID END) AS May,
Count(CASE MONTH(OrderDate) WHEN 6 THEN OrderID END) AS Jun,
Count(CASE MONTH(OrderDate) WHEN 7 THEN OrderID END) AS Jul,
Count(CASE MONTH(OrderDate) WHEN 8 THEN OrderID END) AS Aug,
Count(CASE MONTH(OrderDate) WHEN 9 THEN OrderID END) AS Sep,
Count(CASE MONTH(OrderDate) WHEN 10 THEN OrderID END) AS Oct,
Count(CASE MONTH(OrderDate) WHEN 11 THEN OrderID END) AS Nov,
Count(CASE MONTH(OrderDate) WHEN 12 THEN OrderID END) AS Dec,
count(customerID) AS Result
FROM Northwind.dbo.Orders
GROUP BY CustomerID
Shuja Ali
October 17th, 2006, 02:30 AM
How about this Select
Month(OrderDate) As [Month], CustomerID, count(*)
From
Orders
Group By
Month(OrderDate), CUSTOMERID
Order By
[Month], CustomerID
letheology
October 19th, 2006, 12:14 PM
cross-tab query tried it on Northwind, hope u get it
SELECT CustomerID,
Count(CASE MONTH(OrderDate) WHEN 1 THEN OrderID END) AS Jan,
Count(CASE MONTH(OrderDate) WHEN 2 THEN OrderID END) AS Feb,
Count(CASE MONTH(OrderDate) WHEN 3 THEN OrderID END) AS Mar,
Count(CASE MONTH(OrderDate) WHEN 4 THEN OrderID END) AS Apr,
Count(CASE MONTH(OrderDate) WHEN 5 THEN OrderID END) AS May,
Count(CASE MONTH(OrderDate) WHEN 6 THEN OrderID END) AS Jun,
Count(CASE MONTH(OrderDate) WHEN 7 THEN OrderID END) AS Jul,
Count(CASE MONTH(OrderDate) WHEN 8 THEN OrderID END) AS Aug,
Count(CASE MONTH(OrderDate) WHEN 9 THEN OrderID END) AS Sep,
Count(CASE MONTH(OrderDate) WHEN 10 THEN OrderID END) AS Oct,
Count(CASE MONTH(OrderDate) WHEN 11 THEN OrderID END) AS Nov,
Count(CASE MONTH(OrderDate) WHEN 12 THEN OrderID END) AS Dec,
count(customerID) AS Result
FROM Northwind.dbo.Orders
GROUP BY CustomerID
Hi anishkan-
Thanks for the code snippet. It's quite close to what I want to do, closer than anything I was getting. It has helped me learn some SQL methods, so thanks a lot!
The code snippet seems to list the count of all transactions ever in the final column, rather than just the count of the columns of the selected months. If I figure out how to fix this, I will post my results.
cjard
October 26th, 2006, 10:00 AM
The code snippet seems to list the count of all transactions ever in the final column, rather than just the count of the columns of the selected months. If I figure out how to fix this, I will post my results.
Of course: the SQL contains the line:
count(customerID) AS Result
as the 14th column. This could jsut as easily be Count(*). Because the report is grouped by CUstomerID, youll basically have the 14th column being a count of all the transactions the customer has ever done. The SQL isnt broken, so I cant see how it can be fixed.
Note that because no year is specified, this query will count all transactions in january for any year. If a company has been trading 5 years, and has done 10 transactions every january, then the january column will show 50 - 10 trans per january in 5 years worth of januarys.
I dont know if SQL Server does grouping sets, but in oracle I would do:
SELECT
YEAR(OrderDate) as TranYear,
CustomerID,
Count(CASE MONTH(OrderDate) WHEN 1 THEN OrderID END) AS Jan,
Count(CASE MONTH(OrderDate) WHEN 2 THEN OrderID END) AS Feb,
Count(CASE MONTH(OrderDate) WHEN 3 THEN OrderID END) AS Mar,
Count(CASE MONTH(OrderDate) WHEN 4 THEN OrderID END) AS Apr,
Count(CASE MONTH(OrderDate) WHEN 5 THEN OrderID END) AS May,
Count(CASE MONTH(OrderDate) WHEN 6 THEN OrderID END) AS Jun,
Count(CASE MONTH(OrderDate) WHEN 7 THEN OrderID END) AS Jul,
Count(CASE MONTH(OrderDate) WHEN 8 THEN OrderID END) AS Aug,
Count(CASE MONTH(OrderDate) WHEN 9 THEN OrderID END) AS Sep,
Count(CASE MONTH(OrderDate) WHEN 10 THEN OrderID END) AS Oct,
Count(CASE MONTH(OrderDate) WHEN 11 THEN OrderID END) AS Nov,
Count(CASE MONTH(OrderDate) WHEN 12 THEN OrderID END) AS Dec,
count(customerID) AS Result
FROM Northwind.dbo.Orders
GROUP BY GROUPING SETS (YEAR(OrderDate), CustomerID),(YEAR(OrderDate))
This would produce a resultset where orccasionally the CustomerID column is blank (null) - this would be the row where that year all the transactions for all companies would be summed up and broken across the columns: