Solved

Can SQL output monthly results horizontally?

Posted on 2006-10-29
8
490 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
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

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

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

Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
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…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

617 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