select based on count
in [Programming SQL]
|
From: roma_victa via SQLMonster.com on 13 Dec 2009 05:47 Hi all I have a table UserName PrinterName PageCount Message criss HP 7 Printed criss Xerox 6 Printed criss menolta 5 printed bob hp 3 printed bob hp 4 Printed I wanted to get thouse users whos Pages count is greater than 10 here criss's page count is 18 (7+6+5) greater than 10 but bob has only printed 7 lesser than 10 how to write a query to get the details of the user who's Pagecount is greater than 10 -- Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/200912/1
From: Erland Sommarskog on 13 Dec 2009 07:05 roma_victa via SQLMonster.com (u56444(a)uwe) writes: > Hi all I have a table > > UserName PrinterName PageCount Message > criss HP 7 Printed > criss Xerox 6 Printed > criss menolta 5 printed > bob hp 3 printed > bob hp 4 Printed > > > I wanted to get thouse users whos Pages count is greater than 10 > > here criss's page count is 18 (7+6+5) greater than 10 > but bob has only printed 7 lesser than 10 > > how to write a query to get the details of the user who's Pagecount is > greater than 10 SELECT ... FROM tbl WHERE UserName IN (SELECT UserName FROM tbl GROUP BY UserName HAVING SUM(PageCount) > 10) -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: roma_victa via SQLMonster.com on 13 Dec 2009 08:44 hi i have tried this query but i get the following error message "No value given for one or more required parameter" Erland Sommarskog wrote: >> Hi all I have a table >> >[quoted text clipped - 12 lines] >> how to write a query to get the details of the user who's Pagecount is >> greater than 10 > >SELECT ... >FROM tbl >WHERE UserName IN (SELECT UserName > FROM tbl > GROUP BY UserName > HAVING SUM(PageCount) > 10) > -- Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/200912/1
From: roma_victa via SQLMonster.com on 13 Dec 2009 08:46 hi i have tried this query but i get the following error message "No value given for one or more required parameter" "SELECT UserName, TotalPageCount = SUM(NumberOfPages) FROM details GROUP BY UserName HAVING(SUM(NumberOfPages) > 10) ORDER BY TotalPageCount DESC" Erland Sommarskog wrote: >> Hi all I have a table >> >[quoted text clipped - 12 lines] >> how to write a query to get the details of the user who's Pagecount is >> greater than 10 > >SELECT ... >FROM tbl >WHERE UserName IN (SELECT UserName > FROM tbl > GROUP BY UserName > HAVING SUM(PageCount) > 10) > -- Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/200912/1
From: roma_victa via SQLMonster.com on 13 Dec 2009 08:46 hi i have tried this query but i get the following error message "No value given for one or more required parameter" Erland Sommarskog wrote: >> Hi all I have a table >> >[quoted text clipped - 12 lines] >> how to write a query to get the details of the user who's Pagecount is >> greater than 10 > >SELECT ... >FROM tbl >WHERE UserName IN (SELECT UserName > FROM tbl > GROUP BY UserName > HAVING SUM(PageCount) > 10) > -- Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/200912/1
|
Next
|
Last
Pages: 1 2 Prev: Nullable Foreign key constriants Next: XML input Parameter C# (Stored Proc) |