tsql help - SQL Server Forums

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)

tsql help

Author

Topic

mikejohnson
Posting Yak Master

153 Posts

Posted - 2004-05-27 : 11:24:09

if i have a table design like this:

intID intStoreID bitComplete vcValue

how do i come up with a list showing me a count of records per storeid, the number of them that show as complete, and the difference in percent for totalcount and complete? like this:

Store TotalCount Complete %Complete
--------------------------------------------------
a 100 10 10%
b 200 50 25%

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-05-27 : 11:31:51

CASE statement needed here.

SELECT	intStoreID
	, COUNT(*)
	, SUM(CASE WHEN bitComplete = 1 THEN 1 ELSE 0 END)
	, COUNT(*)/(SUM(CASE WHEN bitComplete = 1 THEN 1 ELSE 0 END)*1.00)

FROM	myTable
GROUP BY intStoreID

You may need to convert the bit datatype for this to work.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources