| Author |
 Topic  |
|
|
bhavtosh
Starting Member
India
4 Posts |
 Posted - 02/25/2012 : 11:42:03
|
hi all,
i have a table with below columns:
studentname, createdby, updatedby
sample data:
s1, user1, user3
s2, user3, user4
s3, user2, user3
can someone pls help me in writing a query that shld return the following output:
username | no. of records created | no. of records update
user1 | 1 | 1
user3 | 1 | 2
user4 | 0 | 1
thanks,
bhavtosh |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 02/25/2012 : 12:27:23
|
SELECT username,SUM(cnt) AS totalcnt
FROM
(
SELECT createdby AS username,count(*) AS cnt
FROM Table
GROUP BY createdby
UNION ALL
SELECT updatedby,count(*) AS cnt
FROM Table
GROUP BY updatedby
)t
GROUP BY username
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
bhavtosh
Starting Member
India
4 Posts |
Posted - 02/25/2012 : 12:42:08
|
your query does NOT work!
i hope u understood my query...
thanks,
bhavtosh |
Edited by - bhavtosh on 02/25/2012 12:42:37 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 02/25/2012 : 13:00:59
|
quote:
Originally posted by bhavtosh
your query does NOT work!
i hope u understood my query...
thanks,
bhavtosh
you've to explain why
also why is user2 missing from output?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Jayam.cnu
Starting Member
India
40 Posts |
Posted - 02/27/2012 : 02:49:53
|
Hi Bhavtosh..... can you try this hope this will reach your result set ...
CREATE TABLE TEST_SQL (studentname VARCHAR(1000), createdby VARCHAR(1000), updatedby VARCHAR(1000) )
INSERT INTO TEST_SQL VALUES
('s1', 'user1', 'user3'),
('s2', 'user3', 'user4'),
('s3', 'user2', 'user3')
SELECT USERNAME ,SUM([no. of records created]) [no. of records created] , SUM([no. of records update]) [no. of records update] FROM
(
SELECT createdby USERNAME ,COUNT( createdby ) [no. of records created],0 [no. of records update] FROM TEST_SQL GROUP BY createdby
UNION
SELECT updatedby USERNAME ,0,COUNT( updatedby ) [no. of records update] FROM TEST_SQL GROUP BY updatedby
) DERIVEDTABLE
GROUP BY USERNAME |
|
|
| |
Topic |
|
|
|
subquery returning multiple results
