Multiple counts using DISTINCT

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Multiple counts using DISTINCT

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-06-03 : 08:15:15

Jeff writes "Is it possible to use one SQL statement to get the following?

I have a table which has columns Category, Item, and DatePosted.

I need to select distinct category, and the total number of items for each category, as well as the total number of items in each category which were posted within the last 5 days. The page will be displayed like this:

Automotive (10) items (3) new
Clothing (8) items (2) new
etc..

Windows,SQL Server 2000 sp4

Thanks!"

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-06-03 : 08:28:24

Something like this??


select
	category,
	sum(items) items,
	sum(case when dateposted between dateadd(dd,-5,dateadd(dd,datediff(dd,0,getdate()),0)) and dateadd(dd,datediff(dd,0,getdate()),0) then items else 0 end) new
from
	tableA
group by
	category

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources