Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Group by month for a chart

Posted on 2005-02-24
6
Medium Priority
?
561 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
  • 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
Industry Leaders: 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 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

569 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