count records within query

[james0816]

I have the following information in table 'Test1':

field1 field 2
000001 000000
000002 000001
000003 000000
000004 000000

I want to write a query that will only display the unique field 2 records and as well provide a count of how many records have that id.

output should look like

field2 recno
000000 3
000001 1

can some one help with the trick to make this work?

thx

 

[feherke]

Hi

[b]select[/b] field2,count(*) [b]as[/b] recno [b]from[/b] test1 [b]group by[/b] field2

Feherke.

http://rootshell.be/~feherke/

 

[james0816]

are you kidding me! ;o) that easy.

sry...been coding all night and brain just not cooperating now. definately think its time for a break.

thx again.

 

[james0816]

ok....misfire....that did work but alas not as i expected. I completely supplied the wrong info.

Let me try this again (Same data as above):

Ouput should ACTUALLY read like this:

Only display records where field2 = 000000 and count those records where field2=field1

field1 records
000001 1
000003 0
000004 0

yes...that's how it should read. I thought it was a little more complicated than your solution. ;o)

thx again.

 

[feherke]

Hi

Only display records where field2 = 000000 and count those records where field2=field1

In your sample data there is no field1=field2. So where the 1 for 000001 is from ?

Feherke.

http://rootshell.be/~feherke/

 

[feherke]

Hi

I try another, maybe I will like it more. ;-)

[b]select[/b] t.field1,t.field1=@rownum:=@rownum+1 records [b]from[/b] (select @rownum:=0) r,test1 t [b]where[/b] field2=[i]'000000'[/i];

http://dev.mysql.com/doc/refman/5.0/en/user-variables.html

Based on the solution provided by Mark Malakanov.

Feherke.

http://rootshell.be/~feherke/

 

[james0816]

This is my sample data from table 'Test1':

field1 field2
-------------------
000001 000000
000002 000001
000003 000000
000004 000000

Here is where I believe it gets tricky:

Part1: Only display those records where field2='000000'
Part2: Count the corresponding records where field2=field1

Output:

field1 reccount
000001 1 <-- 1 for one found match in field2
000003 0
000004 0

I do believe that is how it should be.

thx again.

 

[james0816]

trying your last code now....

 

[feherke]

Hi

Got it ! I think...

[b]select[/b] field1,([b]select[/b] count(*) [b]from[/b] test1 [b]where[/b] field2=t.field1) reccount [b]from[/b] test1 t [b]where[/b] field2=[i]'000000'[/i];

Feherke.

http://rootshell.be/~feherke/

 

[feherke]

Hi

And the faster version of the above :

[b]select[/b] t.field1,sum(if(t2.field2 [b]is not null[/b],1,0)) reccount [b]from[/b] test1 t [b]left join[/b] test1 t2 [b]on[/b] t.field1=t2.field2 [b]where[/b] t.field2=[i]'000000'[/i] [b]group by[/b] t.field1;

Feherke.

http://rootshell.be/~feherke/

 

[james0816]

I tried the first one you posted and it worked until I added more records to the table. i stays at the same out put.

let me look at your second one.

 

[feherke]

Hi

Both should have the same resulte, just the first is slower on big amount of data.

Feherke.

http://rootshell.be/~feherke/

 

[james0816]

feherke....just wanted to thank you again for your assistance on the code. I wound up using the second script. Works great! Back in the office now and the code is flowing once again.

 

[shadedecho]

i think this would work and be even a little more efficient:

select t.field1,count(*) reccount from test1 t join test1 t2 on t.field1=t2.field2 where t.field2='000000' group by t.field1;

the theory here is that you only need to count fields where the JOIN is succesful (ie, there is a field2 matching the given field1)... the previous solution did a left-join, which gets all matches, and then applies an IF to filter out those records in the SUM statement by passing it a 0.

Instead, let the query engine only feed records to count(*) that have already matched the JOIN ON clause successfully, and you get what you want, without having to do extra work to filter out that which you don't want.

result should be the same, but i believe may be quite a bit (relatively speaking) more efficient, and even more so as the data set grows (assuming indexes are maintained appropriately).

 

[feherke]

Hi

shadedecho, that does not return the result james0816 specified. You are right, is more efficient, but I do not understand why do you think is a solution.

[blue]mysql>[/blue] select t.field1,count(*) reccount from test1 t join test1 t2 on t.field1=t2.field2 where t.field2='000000' group by t.field1;
+--------+----------+
| field1 | reccount |
+--------+----------+
| 000001 |        1 |
+--------+----------+
1 row in set (0.01 sec)

Feherke.

http://rootshell.be/~feherke/

 

[shadedecho]

ahh... so you need the 0 count rows? I guess I didn't pick up on that. Sorry. Then yes, the left-join method as specified would be the most efficient way to do it, since the JOIN will not include records for which there is no match (ie, the 0 rows). apologize for the confusion.