subquery returning multiple results

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

General SQL Server Forums

New to SQL Server Programming

subquery returning multiple results

Author

Topic

bhavtosh
Starting Member

4 Posts

Posted - 2012-02-25 : 11:42:03

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-25 : 12:27:23

[code]
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
[/code]

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

bhavtosh
Starting Member

4 Posts

Posted - 2012-02-25 : 12:42:08

your query does NOT work!

i hope u understood my query...

thanks,
bhavtosh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-25 : 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

45 Posts

Posted - 2012-02-27 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources