asked on

Can SQL output monthly results horizontally?

Hi, this a VERY long shot but here's what I'm trying to do:

I have a table which records visits. A visit is recorded simply by writing the date (without the time) to the table.

What I need is to count the hits by year and by month and display them. I'm trying to get it to work in MS Access first.

I can display the results VERTICALLY using:

SELECT Year([VisitDate]) AS [The Year], Month([VisitDate]) AS [The Month], Count(*) AS [Total Number]
FROM Table1
GROUP BY Year([VisitDate]), Month([VisitDate])
ORDER BY Year([VisitDate]), Month([VisitDate]);

My question is...can it be done horizontally so that the output looks like:

Year      Jan      Feb      Mar      Apr etc
2005      10       15        20        25
2006      15       20        25        30

Thanks for reading this

SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

nmcdermaid

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kbit

ASKER

Many thanks for those answers...cant believe anyone even replied!

nmcdermaid,
I tried the crosstab wizard as you suggested and with a few manual changes to the MS Access-generated SQL code, I got it to work perfectly. HOWEVER, it uses the word a keyword "PIVOT" which I'm pretty sure is exclusive to MS Access and not reuseable with mySQL / SQL Server. You'll get half the points though for pointing out a very useful feature.

angelIII,
I've tried your answer in MS Access and I get this error:

Syntax error (missing operator) in query expression 'sum( case when datepart(month,[VisitDate]) = 1 then 1 end )'

Guy Hengel [angelIII / a3]

instead of
datepart(month,[VisitDate])
use
datepart("M", [VisitDate] )

kbit

ASKER

Still same problem

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

nmcdermaid

Sorry, I thought I was in the MS Access forum, my mistake. :)

Pivoting is a bugbear in any database platform and everyone here will tell you to that it is generally better done in the presentation layer rather than the database layer. It really is a presentation thing. Fom a relational database processing point of view, there is no reason to pivot the data like that.

Glad I could help anyway.

kbit

ASKER

Fantastic, I got it to work in both MS Access:

SELECT Year([VisitDate]) AS [The Year]
, sum( iif ( datepart("m",VisitDate) = 1 , 1 , 0 ) ) AS JAN
, sum( iif ( datepart("m",VisitDate) = 2 , 1 , 0 ) ) AS FEB
, sum( iif ( datepart("m",VisitDate) = 3 , 1 , 0 ) ) AS MAR
, sum( iif ( datepart("m",VisitDate) = 4 , 1 , 0 ) ) AS APR
, sum( iif ( datepart("m",VisitDate) = 5 , 1 , 0 ) ) AS MAY
, sum( iif ( datepart("m",VisitDate) = 6 , 1 , 0 ) ) AS JUN
, sum( iif ( datepart("m",VisitDate) = 7 , 1 , 0 ) ) AS JUL
, sum( iif ( datepart("m",VisitDate) = 8 , 1 , 0 ) ) AS AUG
, sum( iif ( datepart("m",VisitDate) = 9 , 1 , 0 ) ) AS SEP
, sum( iif ( datepart("m",VisitDate) = 10 , 1 , 0 ) ) AS OCT
, sum( iif ( datepart("m",VisitDate) = 11 , 1 , 0 ) ) AS NOV
, sum( iif ( datepart("m",VisitDate) = 12 , 1 , 0 ) ) AS DEC
FROM Table1
GROUP BY Year([VisitDate])
ORDER BY Year([VisitDate]);

and mySQL (where DEC seems to be a reserved word):

SELECT Year(VisitDate) AS TheYear,
sum( case when (month(VisitDate) = 1) then 1 end ) AS JAN,
sum( case when (month(VisitDate) = 2) then 1 end ) AS FEB,
sum( case when (month(VisitDate) = 3) then 1 end ) AS MAR,
sum( case when (month(VisitDate) = 4) then 1 end ) AS APR,
sum( case when (month(VisitDate) = 5) then 1 end ) AS MAY,
sum( case when (month(VisitDate) = 6) then 1 end ) AS JUN,
sum( case when (month(VisitDate) = 7) then 1 end ) AS JUL,
sum( case when (month(VisitDate) = 8) then 1 end ) AS AUG,
sum( case when (month(VisitDate) = 9) then 1 end ) AS SEP,
sum( case when (month(VisitDate) = 10) then 1 end ) AS OCT,
sum( case when (month(VisitDate) = 11) then 1 end ) AS NOV,
sum( case when (month(VisitDate) = 12) then 1 end ) AS DECE
FROM Table1
GROUP BY Year(VisitDate)
ORDER BY Year(VisitDate)

Thanks ever so much for the answers