Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 16th, 2005, 09:37 PM
Registered User
 
Join Date: Dec 2005
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default need help for sql COUNT

hi everyone,

i have problem with writing this sql

say i have a table like this:

a b c d
-----------
1 2 1 3
4 2 1 4
1 2 4 3

i want to write a sql to count how many rows(as count1) are (a=1 & b=2) and how many rows(as count2) are (c=1), also group by d
so the result should be:

d=3: count1=2 count2=1
d=4: count1=0 count2=1

how to write this sql??

 
Old December 19th, 2005, 05:11 AM
Friend of Wrox
 
Join Date: Dec 2005
Posts: 132
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to nalla Send a message via Yahoo to nalla
Default



Hi,

To count how many rows are (a=1 & b=2),
SELECT count(*) FROM #table1 WHERE a=1 and b=2

To count how many rows are (c=1)
SELECT count(*) FROM #table1 WHERE c=1

To count how many rows are (a=1 & b=2 & c=1)
SELECT count(*) FROM #table1 WHERE a=1 AND b=2 AND c=1

Please tell if this is not what you wanted or if you want more details.

nalla


 
Old December 19th, 2005, 02:05 PM
Registered User
 
Join Date: Dec 2005
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

well, what i want to know is if there is a way to use *one* sql statement to return both counts instead of those two?



SELECT count(*) FROM #table1 WHERE a=1 and b=2
SELECT count(*) FROM #table1 WHERE c=1




 
Old December 20th, 2005, 12:51 AM
Friend of Wrox
 
Join Date: Dec 2005
Posts: 132
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to nalla Send a message via Yahoo to nalla
Default


Hi,

You can't get those 2 from a single SQL statement.

nalla.


 
Old December 29th, 2005, 05:26 PM
Registered User
 
Join Date: Dec 2005
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

single SQL statement:

SELECT a.count1, b.count2
FROM
(SELECT count(*)as count1 FROM #table1 WHERE a=1 and b=2)a,
(SELECT count(*)as count2 FROM #table1 WHERE c=1) b





Similar Threads
Thread Thread Starter Forum Replies Last Post
sql count problem keyvanjan Classic ASP Basics 1 May 5th, 2006 09:37 AM
sql COUNT problem jordancrandall SQL Language 6 October 18th, 2004 10:27 AM
SQL query for count programmer_kay ADO.NET 1 April 18th, 2004 02:48 AM
SQL query for count programmer_kay SQL Language 1 April 17th, 2004 07:30 PM
sql Count winnie1778 SQL Server 2000 7 November 7th, 2003 02:01 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.