Solved

Can SQL output monthly results horizontally?

Posted on 2006-10-29
8
483 Views
Last Modified: 2009-12-16
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
0
Comment
Question by:kbit
  • 3
  • 3
  • 2
8 Comments
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 250 total points
ID: 17830179
SELECT Year([VisitDate]) AS [The Year]
, sum( case when datepart(month,VisitDate) = 1 then 1 end ) AS JAN
, sum( case when datepart(month,VisitDate) = 2 then 1 end ) AS FEB
, sum( case when datepart(month,VisitDate) = 3 then 1 end ) AS MAR
, sum( case when datepart(month,VisitDate) = 4 then 1 end ) AS APR
, sum( case when datepart(month,VisitDate) = 5 then 1 end ) AS MAI
, sum( case when datepart(month,VisitDate) = 6 then 1 end ) AS JUN
, sum( case when datepart(month,VisitDate) = 7 then 1 end ) AS JUL
, sum( case when datepart(month,VisitDate) = 8 then 1 end ) AS AUG
, sum( case when datepart(month,VisitDate) = 9 then 1 end ) AS SEP
, sum( case when datepart(month,VisitDate) = 10 then 1 end ) AS OCT
, sum( case when datepart(month,VisitDate) = 11 then 1 end ) AS NOV
, sum( case when datepart(month,VisitDate) = 12 then 1 end ) AS DEC
FROM Table1
GROUP BY Year([VisitDate])
ORDER BY Year([VisitDate]);
0
 
LVL 30

Assisted Solution

by:nmcdermaid
nmcdermaid earned 250 total points
ID: 17830221
You can also go through the crosstab wizard in MS Access.
0
 

Author Comment

by:kbit
ID: 17830375
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 )'
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17830387
instead of
datepart(month,[VisitDate])
use
datepart("M", [VisitDate] )

0
 

Author Comment

by:kbit
ID: 17830395
Still same problem
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 17830436

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 JA
, 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 MAI
, 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]);
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 17831056
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.

0
 

Author Comment

by:kbit
ID: 17832958
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
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question