count(*)? was: Re: Query Question...
Hi , This is a little off topic but I have seen count(*) on this list afew times and it got me wondering... Is there a reason to use SELECT COUNT(*) as opposed to SELECT COUNT(column)? I have noticed that selecting count(*) versus specifying the column name executes much more slowly. I've been around, on relative periphery, SQL and SQL DBMS' for a long time but never seen count(*) used. These days I am more involved with SQL (using MySQL) and want to learn more nuances as I go along. Cheers and thanks in advance, Nic On 7/16/05, stipe42 [EMAIL PROTECTED] wrote: Jack Lauman wrote: Given the following query, how can it be modified to return 'Cuisine'(s) that have no rows as having a count of zero and also return a SUM of COUNT(*)? SELECT w.WebsiteName, r.Cuisine, COUNT(*) FROM Restaurant r JOIN RestaurantWebsites w ON r.RestaurantID = w.RestaurantID WHERE w.WebsiteName = 'TOW' GROUP BY w.WebsiteName, r.Cuisine Thanks. Jack After the where clause tack on: HAVING COUNT(*)0 This will eliminate the zero rows, but won't get you the sum. I think the only way to get the sum is to either run a second query, or to sum up the count column in your code as you're looping through the results. stipe42 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: count(*)? was: Re: Query Question...
I believe the difference is that count(*) includes nulls (because it is counting the number of records), whereas count(column) only counts the records where the column being counted is not null, regardless of the total number of rows. Hmm, on a related question then if I am correct above, does count(distinct column) count NULL as a distinct value or not? I.e. if I've got four records in a table with one column: (null, a, a, b), will count(distinct column) return 3 or 2? stipe42 Nic Stevens wrote: Hi , This is a little off topic but I have seen count(*) on this list afew times and it got me wondering... Is there a reason to use SELECT COUNT(*) as opposed to SELECT COUNT(column)? I have noticed that selecting count(*) versus specifying the column name executes much more slowly. I've been around, on relative periphery, SQL and SQL DBMS' for a long time but never seen count(*) used. These days I am more involved with SQL (using MySQL) and want to learn more nuances as I go along. Cheers and thanks in advance, Nic On 7/16/05, stipe42 [EMAIL PROTECTED] wrote: Jack Lauman wrote: Given the following query, how can it be modified to return 'Cuisine'(s) that have no rows as having a count of zero and also return a SUM of COUNT(*)? SELECT w.WebsiteName, r.Cuisine, COUNT(*) FROM Restaurant r JOIN RestaurantWebsites w ON r.RestaurantID = w.RestaurantID WHERE w.WebsiteName = 'TOW' GROUP BY w.WebsiteName, r.Cuisine Thanks. Jack After the where clause tack on: HAVING COUNT(*)0 This will eliminate the zero rows, but won't get you the sum. I think the only way to get the sum is to either run a second query, or to sum up the count column in your code as you're looping through the results. stipe42 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: count(*)? was: Re: Query Question...
stipe42 wrote: I believe the difference is that count(*) includes nulls (because it is counting the number of records), whereas count(column) only counts the records where the column being counted is not null, regardless of the total number of rows. Right. COUNT(*) counts rows, COUNT(col) counts non-null values in col. Hmm, on a related question then if I am correct above, does count(distinct column) count NULL as a distinct value or not? I.e. if I've got four records in a table with one column: (null, a, a, b), will count(distinct column) return 3 or 2? COUNT() doesn't count NULLS. SELECT DISTINCT col FROM yourtable will return NULL, 'a', and 'b', but SELECT COUNT(DISTINCT col) FROM yourtable will return 2, because there are 2 non-NULL values to count. This is easily verified: mysql SELECT * FROM news; ++-+ | id | subject | ++-+ | 1 | cars| | 2 | toys| | 3 | books | | 4 | NULL| | 5 | toys| ++-+ 5 rows in set (0.00 sec) mysql SELECT COUNT(DISTINCT subject) FROM news; +-+ | COUNT(DISTINCT subject) | +-+ | 3 | +-+ 1 row in set (0.00 sec) mysql SELECT subject, COUNT(*), COUNT(subject) FROM news GROUP BY subject; +-+--++ | subject | COUNT(*) | COUNT(subject) | +-+--++ | NULL|1 | 0 | | books |1 | 1 | | cars|1 | 1 | | toys|2 | 2 | +-+--++ 4 rows in set (0.00 sec) stipe42 Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
