How to use count() function? - 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

General SQL Server Forums

New to SQL Server Programming

How to use count() function?

Author

Topic

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2008-04-17 : 00:27:42

Dear all,

I have a table with the the following columns:

ProductCode varchar(50) PK,
ProductType varchar(50),
ProductCategory varchar(50),
InStock (bit),
Locked (bit)

What I want to do is to query this table to return me a list of product type and category, and the total number of products in each category, and the count of number of instock, and locked.

i.e. Type, Category, TotalCount, NumberInStock, NumberNoStock, NumberLocked, NumberUnlocked.

How do I do this in a query?

Any help is very much appreciated.

Eugene

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-17 : 00:35:16

SELECT ProductType AS [Type],ProductCategory AS [Category],COUNT(ProductCode) AS TotalCount,
SUM(CASE WHEN Instock=1 THEN 1 ELSE 0 END) AS NumberInStock,
SUM(CASE WHEN Instock=0 THEN 1 ELSE 0 END) AS NumberNoStock,
SUM(CASE WHEN Locked =1 THEN 1 ELSE 0 END) AS NumberLocked,
SUM(CASE WHEN Locked =0 THEN 1 ELSE 0 END) AS NumberUnlocked
FROM yourTable
GROUP BY ProductType,ProductCategory

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2008-04-17 : 01:35:36

thank you visakh16. appreciate it

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2008-04-17 : 01:57:23

Not working. It returns same number in stock, numbernostock, numberlocked and numberunlock for each group :(

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-17 : 02:28:57

Please post some proper sample data and we'll give it a try again.

E 12°55'05.25"
N 56°04'39.16"

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2008-04-17 : 20:53:06

I apologise, it seems that the code does work in MSSQL. I made a mistake when I try to modified the case when clause to work with my database. Sorry for the mistake and all inconvinience caused.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources