Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sql query count Expand / Collapse
Author
Message
Posted Friday, April 19, 2013 12:10 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 5:22 PM
Points: 58, Visits: 197
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.
Post #1444553
Posted Friday, April 19, 2013 12:13 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 9:50 PM
Points: 727, Visits: 1,410
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
Post #1444554
Posted Friday, April 19, 2013 12:50 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:21 PM
Points: 13,083, Visits: 11,918
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1444563
Posted Friday, April 19, 2013 2:09 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 5:22 PM
Points: 58, Visits: 197
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 ?


Post #1444600
Posted Friday, April 19, 2013 2:12 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:21 PM
Points: 13,083, Visits: 11,918
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1444602
Posted Monday, April 22, 2013 1:01 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 5:22 PM
Points: 58, Visits: 197
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 !
Post #1445123
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse