Group count for each user - 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 2008 Forums

Transact-SQL (2008)

Group count for each user

Author

Topic

keyursoni85
Posting Yak Master

233 Posts

Posted - 2011-12-20 : 06:46:13

[code]

Below is my query, which returns total user wise count for category and category count added by user.
But I also want each user's total count wheather it may different category.

Below query returns (Last three columns - Userwise count, Category wise count by user, total count of each user).
253 Motherboard 2 clarion 39 2 1 2
253 Base Unit 12 clarion 39 2 1 2
253 Processor (CPU) 1 bsoni 2575 3 2 2
253 Motherboard 2 bsoni 2575 3 1 2

BUT - WHEN I pass to fetch record for any single user then also it returns total 2 count but single user passed so there should be 1 count. Am I wrong?

SELECT *, COUNT(1) OVER (PARTITION BY CREATEDUSER) TotalUsers FROM
(
SELECT DISTINCT OPV.PropertyID, PC.PartCategoryName, PC.PartCategoryID, OPV.Value, opv.CreatedUser,
COUNT(1) OVER (PARTITION BY OPV.CreatedUser) UserWiseCount,
COUNT(1) OVER (PARTITION BY PM.partCategoryID, OPV.CreatedUser) CategoryWiseCount
FROM tbl_Units U
INNER JOIN tbl_ObjectPropertyValues OPV ON OPV.ObjectID = U.UID
INNER JOIN tbl_Properties P ON P.PropertyID = OPV.PropertyID AND OPV.PropertyID = 253

INNER JOIN tbl_PartsMaster PM ON PM.ID = U.OEMID
INNER JOIN tbl_PartCategories PC ON PC.PartCategoryID = PM.partCategoryID

INNER JOIN tbl_ActivityRequest AR ON U.ACTID = AR.ActID
) D
SELECT *, COUNT(1) OVER (PARTITION BY CREATEDUSER) TotalUsers FROM
(
SELECT DISTINCT OPV.PropertyID, PC.PartCategoryName, PC.PartCategoryID, OPV.Value, opv.CreatedUser,
COUNT(1) OVER (PARTITION BY OPV.CreatedUser) UserWiseCount,
COUNT(1) OVER (PARTITION BY PM.partCategoryID, OPV.CreatedUser) CategoryWiseCount
FROM tbl_Units U
INNER JOIN tbl_ObjectPropertyValues OPV ON OPV.ObjectID = U.UID AND OPV.CreatedUser = 39
INNER JOIN tbl_Properties P ON P.PropertyID = OPV.PropertyID AND OPV.PropertyID = 253

INNER JOIN tbl_PartsMaster PM ON PM.ID = U.OEMID
INNER JOIN tbl_PartCategories PC ON PC.PartCategoryID = PM.partCategoryID

INNER JOIN tbl_ActivityRequest AR ON U.ACTID = AR.ActID
) TempData

[/code]

keyursoni85
Posting Yak Master

233 Posts

Posted - 2011-12-20 : 07:17:43

There is another data sample I come up with other new user.

 (Here I want 3 count as I have three user below (clarion, bsoni, tsname))

253	Motherboard	2	clarion	39	3	1	3
253	Base Unit	12	clarion	39	3	1	3
253	Laptop AC Adapter	19	clarion	39	3	1	3
253	Processor (CPU)	1	bsoni	2575	3	2	2
253	Motherboard	2	bsoni	2575	3	1	2
253	Optical Drive	3	tsname	2576	1	1	1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-20 : 07:24:40

just need to add this to final select
count(username) over ()

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources