Solved

How to group query by x unit

Posted on 2012-04-13
2
147 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
[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
2 Comments
 
LVL 37

Accepted Solution

by:
momi_sabag earned 500 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

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!

Question has a verified solution.

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

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

749 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