Subqueries, count, etc. - 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)

Subqueries, count, etc.

Author

Topic

zanthony
Starting Member

3 Posts

Posted - 2005-06-03 : 09:55:08

I'm trying desperately to figure out how to achieve the following - any help would be much appreciated.

ProductID Basic Intermediate Total Products
1 3 2 5
2 1 0 1
3 5 3 8

That is, a count of how many of each product are tagged as 'basic' and 'intermediate', plus the total number of products that exist regardless. (Table structure and a query are below.)

I can get the total products, I can get a count of how many of each product are 'basic' or 'intermediate', but I can't seem to put it all together. Here is a rough idea of what I want:

------------------------
SELECT
ProductID,
COUNT(SELECT ID FROM Levels WHERE ID = 1 AND ProductID = 345) AS Basic,
COUNT(SELECT ID FROM Levels WHERE ID = 2 AND ProductID = 345) AS Intermediate,
COUNT(SELECT ID FROM Levels WHERE ID IN (1,2) AND ProductID = 345) AS TotalCount
FROM
Products
WHERE
ProductID = 345
------------------------

But it seems I can't do a count on a subquery. What I'd really like is to get a count of the number of 'hits' for each of the nested subqueries.

Table structure
The ServiceLevel of each product is linked to it via NCContracts, hence the joins.

I'm trying to get this all into one query because I'd like to avoid running two simpler queries multiple times - this will be used to build a long list of products.

Thanks in advance!

Zach

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-03 : 10:18:49

See if this works
SELECT Distinct ProductID,(SELECT count(ID) FROM Levels WHERE ID = 1 AND ProductID = 345) AS Basic,
(SELECT count(ID) FROM Levels WHERE ID = 2 AND ProductID = 345) AS Intermediate,
(SELECT count(ID) FROM Levels WHERE ID IN (1,2) AND ProductID = 345) AS TotalCount
FROM
Products
WHERE
ProductID = 345

Madhivanan

Failing to plan is Planning to fail

zanthony
Starting Member

3 Posts

Posted - 2005-06-03 : 10:33:19

Hi Madhivanan,

Thanks for the help. The layout of the results looks right, but its still not quite working. In all cases the count for Basic and Intermediate is the same. Eg:

ProductID Basic Intermediate TotalCount
1 1 1 2
2 1 1 2
3 1 1 2

Even though in actuality only product 3 is both 'basic' and 'intermediate'.

Any ideas?

quote:
Originally posted by madhivanan

See if this works
SELECT Distinct ProductID,(SELECT count(ID) FROM Levels WHERE ID = 1 AND ProductID = 345) AS Basic,
(SELECT count(ID) FROM Levels WHERE ID = 2 AND ProductID = 345) AS Intermediate,
(SELECT count(ID) FROM Levels WHERE ID IN (1,2) AND ProductID = 345) AS TotalCount
FROM
Products
WHERE
ProductID = 345

Madhivanan

Failing to plan is Planning to fail

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-06-03 : 11:30:00

to conditionally count a row, use:

SUM(CASE WHEN condition THEN 1 ELSE 0 END) as ConditionalCount

Don't use COUNT...

- Jeff

zanthony
Starting Member

3 Posts

Posted - 2005-06-03 : 13:45:29

BRILLIANT! Thanks for the help Jeff, I've got it working now.

quote:
Originally posted by jsmith8858

to conditionally count a row, use:

SUM(CASE WHEN condition THEN 1 ELSE 0 END) as ConditionalCount

Don't use COUNT...

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources