Conditional Count - 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 2005 Forums

Transact-SQL (2005)

Conditional Count

Author

Topic

sandsingh
Starting Member

11 Posts

Posted - 2010-03-29 : 02:11:33

Table Name: BufTable
Structure/Data:

UserID Category
U1 Category A
U1 Category A
U1 Category C
U2 Category A
U2 Category C
U2 Category D
U3 Category A
U3 Category C
U3 Category D

Desired Result:
UserID Count of Category A Count of Category B Count of Category C Count of Category D
U1 2 0 1 0 etc.

Help!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-29 : 02:19:24

use PIVOT http://msdn.microsoft.com/en-us/library/ms177410.aspx

KH
[spoiler]Time is always against us[/spoiler]

sandsingh
Starting Member

11 Posts

Posted - 2010-03-29 : 02:28:29

Thank you. The example on MSDN takes into consideration only 5 employees. What if I don't know how many employees exist? Is there a way to "Group" the users? FYI, I would be using this query as a part of one of my reports in a VB application.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-29 : 02:31:53

if the number of category is unknown then you will need to use dynamic sql with this.

KH
[spoiler]Time is always against us[/spoiler]

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-29 : 02:34:06

see this,
http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

sandsingh
Starting Member

11 Posts

Posted - 2010-03-29 : 03:06:29

Syntax error in FROM clause.

What's incorrect in this query? (Kindly bear with me, I am sort of a newbie)

Select UserID, [Category A] as CatA, [Category B] as CatB, [Category C] as CatC, [Category D] as CatD FROM (SELECT Category, UserID From BufTable) P PIVOT (COUNT(Category) For UserID IN ([Category A], [Category B], [Category C], [Category D])) as Pvt

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-29 : 03:23:37

quote:
Originally posted by sandsingh

Syntax error in FROM clause.

What's incorrect in this query? (Kindly bear with me, I am sort of a newbie)

Select UserID, [Category A] as CatA, [Category B] as CatB, [Category C] as CatC, [Category D] as CatD FROM (SELECT Category, UserID From BufTable) P PIVOT (COUNT(Category) For UserID IN ([Category A], [Category B], [Category C], [Category D])) as Pvt

did you see the link(ref:above)?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-29 : 03:27:26

[code]
Select UserID,
[Category A] as CatA, [Category B] as CatB, [Category C] as CatC, [Category D] as CatD
FROM (
SELECT Category, UserID
From BufTable
) P
PIVOT
(
COUNT(Category)
For Category IN ([Category A], [Category B], [Category C], [Category D])
) as Pvt
[/code]

KH
[spoiler]Time is always against us[/spoiler]

sandsingh
Starting Member

11 Posts

Posted - 2010-03-29 : 04:03:15

Unfortunately, I still get the same error :(
And I also tried running that query directly on an access table. The error selects the word "Pivot" and throws up the message "Syntax Error in FROM clause"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-29 : 04:28:46

What is the version of SQL Server you are using ?

run this and see

print @@version

KH
[spoiler]Time is always against us[/spoiler]

sandsingh
Starting Member

11 Posts

Posted - 2010-03-29 : 04:31:15

I am sorry for not mentioning this earlier. I am trying to run this query on an MS Access 2007 database.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-29 : 04:34:06

quote:
Originally posted by sandsingh

I am sorry for not mentioning this earlier. I am trying to run this query on an MS Access 2007 database.

then post it in access forums rather than in T-SQL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-29 : 04:34:25

you have posted in a SQL Server 2005 forum, the solution is for SQL Server. Try posting over at the MS Access Forum

KH
[spoiler]Time is always against us[/spoiler]

sandsingh
Starting Member

11 Posts

Posted - 2010-03-30 : 07:54:40

Sorry guys, I would be using this query once I migrate my Access database to SQL in a week. Thought it would be helpful to post my query in SQL forum too.

Thank you anyways!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-30 : 08:00:51

the solution for MS Access and SQL Server most of the time will be different. If you will be using SQL Server, the solution posted here should works for you

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources