How to count and assign values in sql? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

How to count and assign values in sql?

Author

Topic

tamancha.1
Starting Member

37 Posts

Posted - 2010-04-02 : 17:14:46

Input:
user_id creative
196450 A
196450 B
196450 C
243011 B
243011 A
245789 A
245789 A
OutPut:
user_id creative (no of times same user_id) (Distinct (creative))
196450 A 3 3
196450 B 3 3
196450 C 3 3
243011 B 2 2
243011 A 2 2
245789 A 2 1
245789 A 2 1

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-04-03 : 01:58:52

See This Thread.

http://visakhm.blogspot.com/search/label/apply%20operator%20sql%202008

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-03 : 02:02:50

[code]SELECT t.user_id,t.creative,t1.CntUser,t1.CreativeCnt
FROM YourTable t
INNER JOIN (SELECT user_id,COUNT(*) AS CntUser,COUNT(DISTINCT creative) AS CreativeCnt
FROM YourTable
GROUP BY user_id)t1
ON t1.user_id = t.user_id
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources