Monthly Counts

[sqltimmy]

I have a table: APPS that lists data for applications for a number of applications including its date. I'm trying to build a query to list number of applications per month by month. I have a query listing the 12 months, and I can make other queries to find count the number of apps per month, but joining all those tables seems like a lot of unncessary work just to find the totals for each month.

Is there an easier way to list the COUNT totals for each month, and by the month in a single query?

Thanks, I appreciate it!

 

[edge2000]

Hi,

Have you tried using the Crosstab function assumeing you are using SQL7/2000.

Look for 'crosstab' under Books On Line.

Cheers Dave Dave
dab@completebs.com

http://www.completebs.com

See website for more info but we can develop most things for most people.

 

[forecasting]

If you didn't find the reference, here are some selections:

Assume there is a table Pivot that has one row per quarter. A SELECT of Pivot reports the quarters vertically:

Year Quarter Amount
---- ------- ------
1990 1 1.1
1990 2 1.2
1990 3 1.3
1990 4 1.4
1991 1 2.1
1991 2 2.2
1991 3 2.3
1991 4 2.4

---To build the app in Northwind

USE Northwind
GO

CREATE TABLE Pivot
( Year SMALLINT,
Quarter TINYINT,
Amount DECIMAL(2,1) )
GO
INSERT INTO Pivot VALUES (1990, 1, 1.1)
INSERT INTO Pivot VALUES (1990, 2, 1.2)
INSERT INTO Pivot VALUES (1990, 3, 1.3)
INSERT INTO Pivot VALUES (1990, 4, 1.4)
INSERT INTO Pivot VALUES (1991, 1, 2.1)
INSERT INTO Pivot VALUES (1991, 2, 2.2)
INSERT INTO Pivot VALUES (1991, 3, 2.3)
INSERT INTO Pivot VALUES (1991, 4, 2.4)
GO

This is the SELECT statement used to create the rotated results:

SELECT Year,
SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS Q1,
SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS Q2,
SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS Q3,
SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS Q4
FROM Northwind.dbo.Pivot
GROUP BY Year
GO


To add a totals column

SELECT P1.*, (P1.Q1 + P1.Q2 + P1.Q3 + P1.Q4) AS YearTotal
FROM (SELECT Year,
SUM(CASE P.Quarter WHEN 1 THEN P.Amount ELSE 0 END) AS Q1,
SUM(CASE P.Quarter WHEN 2 THEN P.Amount ELSE 0 END) AS Q2,
SUM(CASE P.Quarter WHEN 3 THEN P.Amount ELSE 0 END) AS Q3,
SUM(CASE P.Quarter WHEN 4 THEN P.Amount ELSE 0 END) AS Q4
FROM Pivot AS P
GROUP BY P.Year) AS P1
GO