Re: help with query to count rows while excluding certain rows

[Larry Martell]

On Fri, Jan 1, 2016 at 9:31 PM, Peter Brawley
 wrote:
> 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

[Larry Martell]

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'

-- 
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

[Peter Brawley]

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

[Larry Martell]

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

[Tomas Abad]

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

[Ananda Kumar]

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

[Tomas Abad]

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

[Martin Rytz]

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

[Martijn Tonies]

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

[Garth Webb]

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

[Michael Stassen]

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

[Dave Torr]

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

[Brian Mansell]

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

[Dave Torr]

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

[Yayati Kasralikar]

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

[Dave Torr]

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

[Michael Stassen]

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?

[Jeremy Zawodny]

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?

[Roy W]

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?

[Mike Hillyer]

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

[Roman Sanchez]

 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

[William R. Mussatto]

 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

[Shyamal Banerjee]

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

[Rick Baranowski]

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

[Rick Baranowski]

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

[Alan McDonald]

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

[R. Hannes Niedner]

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

[Ed Reed]

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

[Murad Nayal]

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

[Alan McDonald]

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

[Rick Baranowski]

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 ?

[Dainius Ramanauskas]

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