?
Solved

Group by month for a chart

Posted on 2005-02-24
6
Medium Priority
?
559 Views
Last Modified: 2010-05-18
Hi,
I need to make a chart with many series, so will need data that look like this:

EndDate       ServiceA   ServiceB   ServiceC   ......etc.
Nov04            3             6              5
Dec04            4             8              7
Jan05            6             12             2

from a single table that looks like this:

Service     EndDate
ServiceB   12/19/2004
ServiceA   12/23/2004
ServiceA   12/24/2004
ServiceC   12/24/2004
....etc.

So the integers in the query are the counts per month that a Service appears

I can do this for one service (ie a two -column query, but not all of them in one go- it's the sort of thing that could be done in MS Access with a cross tab query.

Please help!
0
Comment
Question by:sumo_the_cat
[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
6 Comments
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 13395395
For a finite number of Services...

SELECT DeliveryYear, DeliveryMonth,
     Max(CASE Service WHEN 'ServiceA' THEN Count ELSE 0 END) AS ServiceA,
     Max(CASE Service WHEN 'ServiceB' THEN Count ELSE 0 END) AS ServiceB,
     ...
FROM
     (SELECT Year(EndDate) AS ServiceYear, Month(EndDate) AS ServiceMonth, Service, Count(*) AS Count
     FROM tblService
     GROUP BY Year(EndDate), Month(EndDate), Service) T
GROUP BY DeliveryYear, DeliveryMonth
ORDER BY DeliveryYear, DeliveryMonth
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 13395412
correciton...

SELECT ServiceYear, ServiceMonth,
     Max(CASE Service WHEN 'ServiceA' THEN Count ELSE 0 END) AS ServiceA,
     Max(CASE Service WHEN 'ServiceB' THEN Count ELSE 0 END) AS ServiceB,
     ...
FROM
     (SELECT Year(EndDate) AS ServiceYear, Month(EndDate) AS ServiceMonth, Service, Count(*) AS Count
     FROM tblService
     GROUP BY Year(EndDate), Month(EndDate), Service) T
GROUP BY ServiceYear, ServiceMonth
ORDER BY ServiceYear, ServiceMonth
0
 
LVL 2

Author Comment

by:sumo_the_cat
ID: 13396209
Thanks! But this requires me to know in advance the services I want columns for - the number is indeed finite, but indefinite, and I won't know the actual names (or IDs). I need a column for every service, no matter how many.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 34

Accepted Solution

by:
Brian Crowe earned 2000 total points
ID: 13396841
look at this...

http://www.sqlteam.com/item.asp?ItemID=2955

You should be able to make it work with something like this...

EXECUTE crosstab 'SELECT Year(EndDate) AS ServiceYear, Month(EndDate) AS ServiceMonth, FROM tblService GROUP BY Year(EndDate), Month(EndDate)', 'Count(EndDate)','Service','tblServices'
0
 
LVL 2

Author Comment

by:sumo_the_cat
ID: 13397354
That looks great, thanks!
0
 
LVL 2

Author Comment

by:sumo_the_cat
ID: 13397596
Just what I needed.
Peter.
0

Featured Post

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

764 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