Solved

Can SQL output monthly results horizontally?

Posted on 2006-10-29
8
476 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 142

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
 
LVL 142

Expert Comment

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

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

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

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Creating and Managing Databases with phpMyAdmin in cPanel.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
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…

747 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now