?
Solved

How to group query by x unit

Posted on 2012-04-13
2
Medium Priority
?
152 Views
Last Modified: 2012-04-17
Hi,

I'm using this little query :
SELECT Sum(PageCount) as NbPage, UserLogon FROM AuditTrail
where CostCentreName = 'Etudiant'
and Timestamp >= convert(datetime, '2011/04/13')
group by UserLogon order by NbPage desc

Open in new window


It show's me how page is printed by user. My SQL is quite old and I can't remember how to "summarize" those data.

I want to know how many users have for exemple :
1 to 500 copies
501 to 1000 copies

etc...

Thx for help.
0
Comment
Question by:Sybux
2 Comments
 
LVL 37

Accepted Solution

by:
momi_sabag earned 2000 total points
ID: 37842746
try

select  case when NbPage between 1 and 500 then '1-500'
                       when NbPage between 501 and 1000 then '501-1000'
                        end + ' Copies' , count(*)
from (
 SELECT Sum(PageCount) as NbPage, UserLogon FROM AuditTrail
 where CostCentreName = 'Etudiant'
 and Timestamp >= convert(datetime, '2011/04/13')
 group by UserLogon order by NbPage desc
) t
group by case when NbPage between 1 and 500 then '1-500'
                       when NbPage between 501 and 1000 then '501-1000'
                        end
0
 

Author Closing Comment

by:Sybux
ID: 37855518
Perfect query. Just a little thing, SQL ask for a TOP in the inside query :)
0

Featured Post

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!

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

809 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