• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 502
  • Last Modified:

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
0
kbit
Asked:
kbit
  • 3
  • 3
  • 2
3 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
nmcdermaidCommented:
You can also go through the crosstab wizard in MS Access.
0
 
kbitAuthor Commented:
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 recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
instead of
datepart(month,[VisitDate])
use
datepart("M", [VisitDate] )

0
 
kbitAuthor Commented:
Still same problem
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:

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
 
nmcdermaidCommented:
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
 
kbitAuthor Commented:
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 Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now