Solved

Can SQL output monthly results horizontally?

Posted on 2006-10-29
8
488 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

Question has a verified solution.

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

Suggested Solutions

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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…

752 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