sql query count

Question

Post reply

sql query count

SqlServerNinja

Ten Centuries

Points: 1238
More actions
April 19, 2013 at 12:10 pm

#104196

I have a table as below .
Id Product1 Product2 Product3
1 1 null null
2 1 2 null
3 3 1 null
4 2 3 1
Now I would like to get a result something like this
ProductID Product1Count Product2Count Product3Count
1 2 1 1
2 1 1 0
3 1 1 0
I could come up till here to get my result for a single product :
select Count(CASE WHEN Product1 = 1 THEN 1 END) +
Count(CASE WHEN Product2 = 1 THEN 1 END) +
Count(CASE WHEN Product3 = 1 THEN 1 END)
from Table1
Could someone tell me how I can include a group by to calculate the count for each of the products.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Fraggle-805517 SSCrazy Eights Points: 8907 More actions · Answer 1

You really want to look at a pivot table for this.

Review these articles:

http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/

Fraggle

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

I don't think you want or need a pivot for this. However to be able to answer your question you first need to help us understand the actual issue. We need to see ddl and sample data. I took a shot at creating those from what you posted.

create table #Something

(

ID int,

Product1 int,

Product2 int,

Product3 int

)

insert #Something

select 1, 1, null, null union all

select 2, 1, 2, null union all

select 3, 3, 1, null union all

select 4, 2, 3, 1

So based on that sample data what should the output be?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

SqlServerNinja Ten Centuries Points: 1238 More actions · Answer 3

Hi Sean !

Thank you once again for trying to help me out .

I actually figured out the solution to the problem.

I did not do a good job of posting the question clearly.

I will do it right now .. and also update the solution.

I was wondering how I can format the code within the question ?

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

SqlServerNinja (4/19/2013)
Hi Sean !
Thank you once again for trying to help me out .
I actually figured out the solution to the problem.
I did not do a good job of posting the question clearly.
I will do it right now .. and also update the solution.
I was wondering how I can format the code within the question ?

You can make those little code boxes by using IFCode shortcuts. They are on the left when you are posting.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

SqlServerNinja Ten Centuries Points: 1238 More actions · Answer 5

Hi Sean

The solution I was looking for is something like this :

SELECT

p.ProductID,

Dept1ProdCount = COUNT(CASE WHEN t.ProductSoldInDept1 = p.ProductID THEN 1 END),

Dept2ProdCount = COUNT(CASE WHEN t.ProductSoldInDept2 = p.ProductID THEN 1 END),

Dept3ProdCount = COUNT(CASE WHEN t.ProductSoldInDept3 = p.ProductID THEN 1 END)

FROM dbo.Product AS p

LEFT OUTER JOIN dbo.[Transaction] AS t

ON p.ProductID IN

(t.ProductSoldInDept1, t.ProductSoldinDept2, t.ProductSoldinDept3)

GROUP BY p.ProductID;

Thank you !