Re: help with query to count rows while excluding certain rows
On Fri, Jan 1, 2016 at 9:31 PM, Peter Brawleywrote: > On 1/1/2016 19:24, Larry Martell wrote: >> >> On Fri, Jan 1, 2016 at 2:12 PM, Peter Brawley >> wrote: >>> >>> On 12/31/2015 0:51, Larry Martell wrote: I need to count the number of rows in a table that are grouped by a list of columns, but I also need to exclude rows that have more then some count when grouped by a different set of columns. Conceptually, this is not hard, but I am having trouble doing this efficiently. My first counting query would be this: SELECT count(*) FROM cst_rollup GROUP BY target_name_id, ep, roiname, recipe_process, recipe_product, recipe_layer, f_tag_bottom, measname, recipe_id But from this count I need to subtract the count of rows that have more then 50 rows with a different grouping: SELECT count(*) FROM cst_rollup GROUP BY target_name_id, ep, wafer_id HAVING count(*) >= 50 As you can see, the second query has wafer_id, but the first query does not. Currently I am doing this in python, and it's slow. In my current implementation I have one query, and it selects the columns (i.e. doesn't just count), and I have added wafer_id: SELECT target_name_id, ep, roiname, recipe_process, recipe_product, recipe_layer, f_tag_bottom, measname, recipe_id, wafer_id FROM cst_rollup Then I go through the result set (which can be over 200k rows) and I count the number of rows with matching (target_name_id, ep, wafer_id). Then I go through the rows again and regroup them without wafer_id, but skipping the rows that have more then 50 rows for that row's (target_name_id, ep, wafer_id). Is this clear to everyone what I am trying to do? >>> >>> >>> If I've understand this correctly, the resultset you wish to aggregate on >>> is >>> ... >>> >>> select target_name_id, ep, wafer_id >>> from cst_rollup a >>> left join ( -- exclude rows for which wafer_id count >= 50 >>>select name_id, ep, wafer, count(*) n >>>from cst_rollup >>>group by target_name_id, ep, wafer_id >>>having n >= 50 >>> ) b using ( target_name_id, ep, wafer_id ) >>> where b.target_name is null ; >>> >>> If that's so, you could assemble that resultset in a temp table then run >>> the >>> desired aggregate query on it, or you could aggregate on it directly as a >>> subquery. >> >> That query gives: >> >> ERROR 1137 (HY000): Can't reopen table: 'a' > > > So, it's a temporary table, and you'll need to make that not so. Yes, cst_rollup is a temp table. The underlying table is millions of rows (with 300 columns) so for efficiency a subset of the rows and columns are selected into the temp table based on some user input. It's just the rows in the temp table that are of interest for the current report. I was able to get this working with a second temp table: CREATE TEMPORARY TABLE rollup_exclude SELECT target_name_id, ep, wafer_id, count(*) n FROM cst_rollup GROUP BY target_name_id, ep, wafer_id HAVING n >= 50 And then: SELECT count(*) FROM cst_rollup LEFT JOIN( SELECT target_name_id, ep, wafer_id FROM rollup_exclude) b USING (target_name_id, ep, wafer_id) WHERE b.target_name_id IS NULL GROUP by target_name_id, ep, roiname, recipe_process, recipe_product, recipe_layer, f_tag_bottom, measname, recipe_id And the rowcount from that query gave me what I needed. Thanks very much for the help Peter, you gave me a push toward the right path. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: help with query to count rows while excluding certain rows
On Fri, Jan 1, 2016 at 2:12 PM, Peter Brawleywrote: > On 12/31/2015 0:51, Larry Martell wrote: >> >> I need to count the number of rows in a table that are grouped by a >> list of columns, but I also need to exclude rows that have more then >> some count when grouped by a different set of columns. Conceptually, >> this is not hard, but I am having trouble doing this efficiently. >> >> My first counting query would be this: >> >> SELECT count(*) >> FROM cst_rollup >> GROUP BY target_name_id, ep, roiname, recipe_process, >> recipe_product, recipe_layer, f_tag_bottom, >> measname, recipe_id >> >> But from this count I need to subtract the count of rows that have >> more then 50 rows with a different grouping: >> >> SELECT count(*) >> FROM cst_rollup >> GROUP BY target_name_id, ep, wafer_id >> HAVING count(*) >= 50 >> >> As you can see, the second query has wafer_id, but the first query does >> not. >> >> Currently I am doing this in python, and it's slow. In my current >> implementation I have one query, and it selects the columns (i.e. >> doesn't just count), and I have added wafer_id: >> >> SELECT target_name_id, ep, roiname, recipe_process, >> recipe_product, recipe_layer, f_tag_bottom, >> measname, recipe_id, wafer_id >> FROM cst_rollup >> >> Then I go through the result set (which can be over 200k rows) and I >> count the number of rows with matching (target_name_id, ep, wafer_id). >> Then I go through the rows again and regroup them without wafer_id, >> but skipping the rows that have more then 50 rows for that row's >> (target_name_id, ep, wafer_id). >> >> Is this clear to everyone what I am trying to do? > > > If I've understand this correctly, the resultset you wish to aggregate on is > ... > > select target_name_id, ep, wafer_id > from cst_rollup a > left join ( -- exclude rows for which wafer_id count >= 50 > select name_id, ep, wafer, count(*) n > from cst_rollup > group by target_name_id, ep, wafer_id > having n >= 50 > ) b using ( target_name_id, ep, wafer_id ) > where b.target_name is null ; > > If that's so, you could assemble that resultset in a temp table then run the > desired aggregate query on it, or you could aggregate on it directly as a > subquery. That query gives: ERROR 1137 (HY000): Can't reopen table: 'a' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: help with query to count rows while excluding certain rows
On 12/31/2015 0:51, Larry Martell wrote: I need to count the number of rows in a table that are grouped by a list of columns, but I also need to exclude rows that have more then some count when grouped by a different set of columns. Conceptually, this is not hard, but I am having trouble doing this efficiently. My first counting query would be this: SELECT count(*) FROM cst_rollup GROUP BY target_name_id, ep, roiname, recipe_process, recipe_product, recipe_layer, f_tag_bottom, measname, recipe_id But from this count I need to subtract the count of rows that have more then 50 rows with a different grouping: SELECT count(*) FROM cst_rollup GROUP BY target_name_id, ep, wafer_id HAVING count(*) >= 50 As you can see, the second query has wafer_id, but the first query does not. Currently I am doing this in python, and it's slow. In my current implementation I have one query, and it selects the columns (i.e. doesn't just count), and I have added wafer_id: SELECT target_name_id, ep, roiname, recipe_process, recipe_product, recipe_layer, f_tag_bottom, measname, recipe_id, wafer_id FROM cst_rollup Then I go through the result set (which can be over 200k rows) and I count the number of rows with matching (target_name_id, ep, wafer_id). Then I go through the rows again and regroup them without wafer_id, but skipping the rows that have more then 50 rows for that row's (target_name_id, ep, wafer_id). Is this clear to everyone what I am trying to do? If I've understand this correctly, the resultset you wish to aggregate on is ... select target_name_id, ep, wafer_id from cst_rollup a left join ( -- exclude rows for which wafer_id count >= 50 select name_id, ep, wafer, count(*) n from cst_rollup group by target_name_id, ep, wafer_id having n >= 50 ) b using ( target_name_id, ep, wafer_id ) where b.target_name is null ; If that's so, you could assemble that resultset in a temp table then run the desired aggregate query on it, or you could aggregate on it directly as a subquery. PB - I'd like to do this all in sql with count because then I do not have to actually return and parse the data in python. Can anyone think of a way to do this in sql in a way that will be more efficient then my current implementation? Thanks! -Larry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
help with query to count rows while excluding certain rows
I need to count the number of rows in a table that are grouped by a list of columns, but I also need to exclude rows that have more then some count when grouped by a different set of columns. Conceptually, this is not hard, but I am having trouble doing this efficiently. My first counting query would be this: SELECT count(*) FROM cst_rollup GROUP BY target_name_id, ep, roiname, recipe_process, recipe_product, recipe_layer, f_tag_bottom, measname, recipe_id But from this count I need to subtract the count of rows that have more then 50 rows with a different grouping: SELECT count(*) FROM cst_rollup GROUP BY target_name_id, ep, wafer_id HAVING count(*) >= 50 As you can see, the second query has wafer_id, but the first query does not. Currently I am doing this in python, and it's slow. In my current implementation I have one query, and it selects the columns (i.e. doesn't just count), and I have added wafer_id: SELECT target_name_id, ep, roiname, recipe_process, recipe_product, recipe_layer, f_tag_bottom, measname, recipe_id, wafer_id FROM cst_rollup Then I go through the result set (which can be over 200k rows) and I count the number of rows with matching (target_name_id, ep, wafer_id). Then I go through the rows again and regroup them without wafer_id, but skipping the rows that have more then 50 rows for that row's (target_name_id, ep, wafer_id). Is this clear to everyone what I am trying to do? I'd like to do this all in sql with count because then I do not have to actually return and parse the data in python. Can anyone think of a way to do this in sql in a way that will be more efficient then my current implementation? Thanks! -Larry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Querry Count Rows
Hi all, I have a table with ordes and i want to know the top 10 Citys have the most ordes. This is my table Order_id, City Need count the rows where haver the most similar text y the column City? Thanks all
Re: Querry Count Rows
can u please give sample data. If the same city has more than one order_id then, you could use this select city,count(*) total from table group by city order by total desc limit 10; regards anandkl On 10/1/07, Tomas Abad [EMAIL PROTECTED] wrote: Hi all, I have a table with ordes and i want to know the top 10 Citys have the most ordes. This is my table Order_id, City Need count the rows where haver the most similar text y the column City? Thanks all
RE: Querry Count Rows
Very well, Works, thanks. -Mensaje original- De: Ananda Kumar [mailto:[EMAIL PROTECTED] Enviado el: lunes, 01 de octubre de 2007 13:16 Para: [EMAIL PROTECTED] CC: mysql@lists.mysql.com Asunto: Re: Querry Count Rows can u please give sample data. If the same city has more than one order_id then, you could use this select city,count(*) total from table group by city order by total desc limit 10; regards anandkl On 10/1/07, Tomas Abad [EMAIL PROTECTED] wrote: Hi all, I have a table with ordes and i want to know the top 10 Citys have the most ordes. This is my table Order_id, City Need count the rows where haver the most similar text y the column City? Thanks all -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Count Rows within unions
Hi SQL-Cracks How can I count the rows within a select with multiple unions? Count rows with one select is easy: select count(*) from table Count rows over multiple tables is complicated: select name from table1 union select name from table2 union select name from table3 order by name How can I count the rows over this 3 tables? Thank you in advance! Martin Rytz
Re: How to COUNT rows when they have a COUNT in them
Just a note... Thanks - this did not work for me as I am on 4.0.17 - presumably this works on 4.1 (seems to need the SubQuery feature)? If so I will upgrade immediately! This isn't a subquery -- this is a Derived Table. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com Following query does what you want: SELECT COUNT(*) from (SELECT COUNT(*) as c FROM pet GROUP BY owner HAVING c1) as temp -Yayati -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to COUNT rows when they have a COUNT in them
You could also try: SELECT owner, COUNT(*) FROM pet GROUP BY owner; SELECT FOUND_ROWS(); On Mon, 2004-06-14 at 20:41, Dave Torr wrote: Thanks - this did not work for me as I am on 4.0.17 - presumably this works on 4.1 (seems to need the SubQuery feature)? If so I will upgrade immediately! From: Yayati Kasralikar [EMAIL PROTECTED] To: Dave Torr [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: How to COUNT rows when they have a COUNT in them Date: Mon, 14 Jun 2004 23:37:15 -0400 Following query does what you want: SELECT COUNT(*) from (SELECT COUNT(*) as c FROM pet GROUP BY owner HAVING c1) as temp -Yayati Dave Torr wrote: Probably simple but I can't figure it out! THe manual section 3.3.4.8 has the example SELECT owner, COUNT(*) FROM pet GROUP BY owner which is fine. Now what I want to do is count the number of rows this returns. Actually of course this is trivial - I can just count how many owners there are. What I actually have is something similar to SELECT owner, COUNT(*) as c FROM pet GROUP BY owner HAVING c1 (ie I want to see the owners who have more than one pet). And I just want to know how many there are - at the moment I am having to retreive the full data set (which is large in my case). What I want is something like SELECT COUNT(SELECT owner, COUNT(*) FROM pet GROUP BY owner HAVING c1) but that doesn't work -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to COUNT rows when they have a COUNT in them
I knew there was a better way than what I suggested! Of course, you'll want to use your actual query for the first line: SELECT COUNT(*) as c FROM pet GROUP BY owner HAVING c 1; SELECT FOUND_ROWS(); Michael Garth Webb wrote: You could also try: SELECT owner, COUNT(*) FROM pet GROUP BY owner; SELECT FOUND_ROWS(); On Mon, 2004-06-14 at 20:41, Dave Torr wrote: Thanks - this did not work for me as I am on 4.0.17 - presumably this works on 4.1 (seems to need the SubQuery feature)? If so I will upgrade immediately! From: Yayati Kasralikar [EMAIL PROTECTED] To: Dave Torr [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: How to COUNT rows when they have a COUNT in them Date: Mon, 14 Jun 2004 23:37:15 -0400 Following query does what you want: SELECT COUNT(*) from (c) as temp -Yayati Dave Torr wrote: Probably simple but I can't figure it out! THe manual section 3.3.4.8 has the example SELECT owner, COUNT(*) FROM pet GROUP BY owner which is fine. Now what I want to do is count the number of rows this returns. Actually of course this is trivial - I can just count how many owners there are. What I actually have is something similar to SELECT owner, COUNT(*) as c FROM pet GROUP BY owner HAVING c1 (ie I want to see the owners who have more than one pet). And I just want to know how many there are - at the moment I am having to retreive the full data set (which is large in my case). What I want is something like SELECT COUNT(SELECT owner, COUNT(*) FROM pet GROUP BY owner HAVING c1) but that doesn't work -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to COUNT rows when they have a COUNT in them
Thanks - that is basically what I used to do (it works fine now on 4.1.2) but it was very slow as there are a LOT of rows and this method returned all of them. From: Garth Webb [EMAIL PROTECTED] To: Dave Torr [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: How to COUNT rows when they have a COUNT in them Date: Tue, 15 Jun 2004 09:54:19 -0700 You could also try: SELECT owner, COUNT(*) FROM pet GROUP BY owner; SELECT FOUND_ROWS(); On Mon, 2004-06-14 at 20:41, Dave Torr wrote: Thanks - this did not work for me as I am on 4.0.17 - presumably this works on 4.1 (seems to need the SubQuery feature)? If so I will upgrade immediately! From: Yayati Kasralikar [EMAIL PROTECTED] To: Dave Torr [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: How to COUNT rows when they have a COUNT in them Date: Mon, 14 Jun 2004 23:37:15 -0400 Following query does what you want: SELECT COUNT(*) from (SELECT COUNT(*) as c FROM pet GROUP BY owner HAVING c1) as temp -Yayati Dave Torr wrote: Probably simple but I can't figure it out! THe manual section 3.3.4.8 has the example SELECT owner, COUNT(*) FROM pet GROUP BY owner which is fine. Now what I want to do is count the number of rows this returns. Actually of course this is trivial - I can just count how many owners there are. What I actually have is something similar to SELECT owner, COUNT(*) as c FROM pet GROUP BY owner HAVING c1 (ie I want to see the owners who have more than one pet). And I just want to know how many there are - at the moment I am having to retreive the full data set (which is large in my case). What I want is something like SELECT COUNT(SELECT owner, COUNT(*) FROM pet GROUP BY owner HAVING c1) but that doesn't work -- 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: How to COUNT rows when they have a COUNT in them
Dave - I would recommend (if not done so already) adding an index on owner. That should improve the speed quite a bit. On Tue, 15 Jun 2004 21:06:47 +, Dave Torr [EMAIL PROTECTED] wrote: Thanks - that is basically what I used to do (it works fine now on 4.1.2) but it was very slow as there are a LOT of rows and this method returned all of them. From: Garth Webb [EMAIL PROTECTED] To: Dave Torr [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: How to COUNT rows when they have a COUNT in them Date: Tue, 15 Jun 2004 09:54:19 -0700 You could also try: SELECT owner, COUNT(*) FROM pet GROUP BY owner; SELECT FOUND_ROWS(); On Mon, 2004-06-14 at 20:41, Dave Torr wrote: Thanks - this did not work for me as I am on 4.0.17 - presumably this works on 4.1 (seems to need the SubQuery feature)? If so I will upgrade immediately! From: Yayati Kasralikar [EMAIL PROTECTED] To: Dave Torr [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: How to COUNT rows when they have a COUNT in them Date: Mon, 14 Jun 2004 23:37:15 -0400 Following query does what you want: SELECT COUNT(*) from (SELECT COUNT(*) as c FROM pet GROUP BY owner HAVING c1) as temp -Yayati Dave Torr wrote: Probably simple but I can't figure it out! THe manual section 3.3.4.8 has the example SELECT owner, COUNT(*) FROM pet GROUP BY owner which is fine. Now what I want to do is count the number of rows this returns. Actually of course this is trivial - I can just count how many owners there are. What I actually have is something similar to SELECT owner, COUNT(*) as c FROM pet GROUP BY owner HAVING c1 (ie I want to see the owners who have more than one pet). And I just want to know how many there are - at the moment I am having to retreive the full data set (which is large in my case). What I want is something like SELECT COUNT(SELECT owner, COUNT(*) FROM pet GROUP BY owner HAVING c1) but that doesn't work -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to COUNT rows when they have a COUNT in them
Probably simple but I can't figure it out! THe manual section 3.3.4.8 has the example SELECT owner, COUNT(*) FROM pet GROUP BY owner which is fine. Now what I want to do is count the number of rows this returns. Actually of course this is trivial - I can just count how many owners there are. What I actually have is something similar to SELECT owner, COUNT(*) as c FROM pet GROUP BY owner HAVING c1 (ie I want to see the owners who have more than one pet). And I just want to know how many there are - at the moment I am having to retreive the full data set (which is large in my case). What I want is something like SELECT COUNT(SELECT owner, COUNT(*) FROM pet GROUP BY owner HAVING c1) but that doesn't work -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to COUNT rows when they have a COUNT in them
Following query does what you want: SELECT COUNT(*) from (SELECT COUNT(*) as c FROM pet GROUP BY owner HAVING c1) as temp -Yayati Dave Torr wrote: Probably simple but I can't figure it out! THe manual section 3.3.4.8 has the example SELECT owner, COUNT(*) FROM pet GROUP BY owner which is fine. Now what I want to do is count the number of rows this returns. Actually of course this is trivial - I can just count how many owners there are. What I actually have is something similar to SELECT owner, COUNT(*) as c FROM pet GROUP BY owner HAVING c1 (ie I want to see the owners who have more than one pet). And I just want to know how many there are - at the moment I am having to retreive the full data set (which is large in my case). What I want is something like SELECT COUNT(SELECT owner, COUNT(*) FROM pet GROUP BY owner HAVING c1) but that doesn't work -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to COUNT rows when they have a COUNT in them
Thanks - this did not work for me as I am on 4.0.17 - presumably this works on 4.1 (seems to need the SubQuery feature)? If so I will upgrade immediately! From: Yayati Kasralikar [EMAIL PROTECTED] To: Dave Torr [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: How to COUNT rows when they have a COUNT in them Date: Mon, 14 Jun 2004 23:37:15 -0400 Following query does what you want: SELECT COUNT(*) from (SELECT COUNT(*) as c FROM pet GROUP BY owner HAVING c1) as temp -Yayati Dave Torr wrote: Probably simple but I can't figure it out! THe manual section 3.3.4.8 has the example SELECT owner, COUNT(*) FROM pet GROUP BY owner which is fine. Now what I want to do is count the number of rows this returns. Actually of course this is trivial - I can just count how many owners there are. What I actually have is something similar to SELECT owner, COUNT(*) as c FROM pet GROUP BY owner HAVING c1 (ie I want to see the owners who have more than one pet). And I just want to know how many there are - at the moment I am having to retreive the full data set (which is large in my case). What I want is something like SELECT COUNT(SELECT owner, COUNT(*) FROM pet GROUP BY owner HAVING c1) but that doesn't work -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to COUNT rows when they have a COUNT in them
Right, subqueries require 4.1. In 4.0.17, you could do this with 2 queries and a temporary table: CREATE TEMPORARY TABLE owners_temp SELECT COUNT(*) as c FROM pet GROUP BY owner HAVING c1; SELECT COUNT(*) FROM owners_temp; DROP TABLE owners_temp; There may be a better way, but that should work. Michael Dave Torr wrote: Thanks - this did not work for me as I am on 4.0.17 - presumably this works on 4.1 (seems to need the SubQuery feature)? If so I will upgrade immediately! From: Yayati Kasralikar [EMAIL PROTECTED] To: Dave Torr [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: How to COUNT rows when they have a COUNT in them Date: Mon, 14 Jun 2004 23:37:15 -0400 Following query does what you want: SELECT COUNT(*) from (SELECT COUNT(*) as c FROM pet GROUP BY owner HAVING c1) as temp -Yayati Dave Torr wrote: Probably simple but I can't figure it out! THe manual section 3.3.4.8 has the example SELECT owner, COUNT(*) FROM pet GROUP BY owner which is fine. Now what I want to do is count the number of rows this returns. Actually of course this is trivial - I can just count how many owners there are. What I actually have is something similar to SELECT owner, COUNT(*) as c FROM pet GROUP BY owner HAVING c1 (ie I want to see the owners who have more than one pet). And I just want to know how many there are - at the moment I am having to retreive the full data set (which is large in my case). What I want is something like SELECT COUNT(SELECT owner, COUNT(*) FROM pet GROUP BY owner HAVING c1) but that doesn't work -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Count Rows?
On Wed, Jul 02, 2003 at 10:57:18AM -0500, Roy W wrote: Is there a simple MySQL command that will give a Row Count (# of records) WITHOUT running a select (huge database) If it's a MyISAM table, just run a SELECT COUNT(*) FROM table_name. It's really efficient. Try it. :-) -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 31 days, processed 979,861,677 queries (357/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Count Rows?
Is there a simple MySQL command that will give a Row Count (# of records) WITHOUT running a select (huge database) Thanks! Roy
RE: Count Rows?
If your table is MyISAM, then SELECT COUNT(*) FROM tablename Will return a rowcount without a major performance hit as the rowcount is stored and a table scan is not needed. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Roy W [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 02, 2003 9:57 AM To: [EMAIL PROTECTED] Subject: Count Rows? Is there a simple MySQL command that will give a Row Count (# of records) WITHOUT running a select (huge database) Thanks! Roy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: count rows
Here's a nice MySQL simple returning records query question! I want to return a staement saying 'number of records shave been found' for your selection. so presumably this is a simple row count based on the the returning recordset? Andrew Either with PHP or C you can use mysql_num_rows() Bye! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: count rows
Here's a nice MySQL simple returning records query question! I want to return a staement saying 'number of records shave been found' for your selection. so presumably this is a simple row count based on the the returning recordset? Andrew Either with PHP or C you can use mysql_num_rows() In perl:$numRows = $sth-rows; Note the following warning from the DBI man page: Generally, you can only rely on a row count after a non-SELECT execute (for some specific operations like UPDATE and DELETE), or after fetching all the rows of a SELECT statement. Bye! William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Count Rows in two tables
If you know the primary keys of the two tables, it may be done as follows: select count(distinct t1.pk1) + count(distinct t2.pk2) from t1,t2; where - pk1 is the primary key column name of table t1 and pk2 is the primary key column name of table t2. SB - Original Message - From: Rick Baranowski [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, November 13, 2002 8:39 AM Subject: Re: Count Rows in two tables Please forgive me I am new to mySQL, could you give me an example of how I would do this? The two tables are users and staff Thanks Rick - Original Message - From: Murad Nayal [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, November 12, 2002 6:58 PM Subject: Re: Count Rows in two tables Alan McDonald wrote: You can't count the join? Alan if you count the (unqualified) join you'll end up with the product of the two table counts. Murad -Original Message- From: Rick Baranowski [mailto:rickb;baranconsulting.com] Sent: Wednesday, 13 November 2002 12:10 To: [EMAIL PROTECTED] Subject: Count Rows in two tables Hello all, Does anybody have a SQL string to count the rows in two different tables and give you a total number of rows? I have been trying to find an answer for a couple of days and seems like a simple string. Thank you Rick - - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Count Rows in two tables
Thank you for the response. It looks like that worked! I am going to try it on the webpage now. I will let everybody know if this works. Thanks Rick PS. to everyone else I want to say thanks. I have run the ones that you have sent but always came up with an error. If you want me to send you the error let me know and I will send it to you. Thanks again - Original Message - From: Shyamal Banerjee [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, November 13, 2002 4:29 AM Subject: Re: Count Rows in two tables If you know the primary keys of the two tables, it may be done as follows: select count(distinct t1.pk1) + count(distinct t2.pk2) from t1,t2; where - pk1 is the primary key column name of table t1 and pk2 is the primary key column name of table t2. SB - Original Message - From: Rick Baranowski [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, November 13, 2002 8:39 AM Subject: Re: Count Rows in two tables Please forgive me I am new to mySQL, could you give me an example of how I would do this? The two tables are users and staff Thanks Rick - Original Message - From: Murad Nayal [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, November 12, 2002 6:58 PM Subject: Re: Count Rows in two tables Alan McDonald wrote: You can't count the join? Alan if you count the (unqualified) join you'll end up with the product of the two table counts. Murad -Original Message- From: Rick Baranowski [mailto:rickb;baranconsulting.com] Sent: Wednesday, 13 November 2002 12:10 To: [EMAIL PROTECTED] Subject: Count Rows in two tables Hello all, Does anybody have a SQL string to count the rows in two different tables and give you a total number of rows? I have been trying to find an answer for a couple of days and seems like a simple string. Thank you Rick - - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Count Rows in two tables
Hello all, Does anybody have a SQL string to count the rows in two different tables and give you a total number of rows? I have been trying to find an answer for a couple of days and seems like a simple string. Thank you Rick - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Count Rows in two tables
You can't count the join? Alan -Original Message- From: Rick Baranowski [mailto:rickb;baranconsulting.com] Sent: Wednesday, 13 November 2002 12:10 To: [EMAIL PROTECTED] Subject: Count Rows in two tables Hello all, Does anybody have a SQL string to count the rows in two different tables and give you a total number of rows? I have been trying to find an answer for a couple of days and seems like a simple string. Thank you Rick - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Count Rows in two tables
On 11/12/02 5:36 PM, Alan McDonald [EMAIL PROTECTED] wrote: You can't count the join? Alan Hello all, Does anybody have a SQL string to count the rows in two different tables and give you a total number of rows? I have been trying to find an answer for a couple of days and seems like a simple string. Thank you Rick I guess he wants something like: SELECT SUM(COUNT(t1.*) , COUNT(t2.*)) FROM table1 t1, table2; Which obviously doesn't work. Maybe once we have subselects we can do it in one query: /h - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Count Rows in two tables
Does it have to be in a single query. You could use a Union query and then total the results in your program. Select Count(Field1) From Table1 Union Select Count(Field1) From Table2; Rick Baranowski [EMAIL PROTECTED] 11/12/02 5:10:14 PM Hello all, Does anybody have a SQL string to count the rows in two different tables and give you a total number of rows? I have been trying to find an answer for a couple of days and seems like a simple string. Thank you Rick - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Count Rows in two tables
Alan McDonald wrote: You can't count the join? Alan if you count the (unqualified) join you'll end up with the product of the two table counts. Murad -Original Message- From: Rick Baranowski [mailto:rickb;baranconsulting.com] Sent: Wednesday, 13 November 2002 12:10 To: [EMAIL PROTECTED] Subject: Count Rows in two tables Hello all, Does anybody have a SQL string to count the rows in two different tables and give you a total number of rows? I have been trying to find an answer for a couple of days and seems like a simple string. Thank you Rick - - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Count Rows in two tables
Ah, yes - sorry Alan -Original Message- From: [EMAIL PROTECTED] [mailto:murad;godel.bioc.columbia.edu]On Behalf Of Murad Nayal Sent: Wednesday, 13 November 2002 12:58 Cc: [EMAIL PROTECTED] Subject: Re: Count Rows in two tables Alan McDonald wrote: You can't count the join? Alan if you count the (unqualified) join you'll end up with the product of the two table counts. Murad -Original Message- From: Rick Baranowski [mailto:rickb;baranconsulting.com] Sent: Wednesday, 13 November 2002 12:10 To: [EMAIL PROTECTED] Subject: Count Rows in two tables Hello all, Does anybody have a SQL string to count the rows in two different tables and give you a total number of rows? I have been trying to find an answer for a couple of days and seems like a simple string. Thank you Rick - - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Count Rows in two tables
Please forgive me I am new to mySQL, could you give me an example of how I would do this? The two tables are users and staff Thanks Rick - Original Message - From: Murad Nayal [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, November 12, 2002 6:58 PM Subject: Re: Count Rows in two tables Alan McDonald wrote: You can't count the join? Alan if you count the (unqualified) join you'll end up with the product of the two table counts. Murad -Original Message- From: Rick Baranowski [mailto:rickb;baranconsulting.com] Sent: Wednesday, 13 November 2002 12:10 To: [EMAIL PROTECTED] Subject: Count Rows in two tables Hello all, Does anybody have a SQL string to count the rows in two different tables and give you a total number of rows? I have been trying to find an answer for a couple of days and seems like a simple string. Thank you Rick - - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
how to count rows in JDBC ResultSet ?
Hello, I want to know the number of rows in a JDBC-ResulSet just by calling a function like it's done in PHP with mysql_num_rows($result) There seems to be no such function in the ResultSet interface as defined in Java 2. cheers Oliver Doepner - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php