Query help

I was wondering if someone could lend a hand with the following query. I h=
ave table.

SEARCHES
|ID |trans_no|comp_id|result
13 | 455 | 675 | o
15 | 302 | 675 | o
16 | 455 | 675 | o
12 | 225 | 629 | y

SELECT count(*) FROM SEARCHES WHERE comp_id=3D675 AND result=3D'o' GROUP BY=
trans_no;

gives me a count of 3.

However, what I need is a count for how many different (unique) transaction=
s company number 675 got a result 'o' which would be 2 (455 & 302). I have=
tried different group by columns but to no avail. Can someone help?

Thanks,

Richard

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

RE: Query help

Depending on whether you just need to count or the transaction numbers, =
one of the following three should get you where you want/need to be:

To identify the count for comp_id =3D 675:
select count(distinct trans_no) from trans where comp_id =3D 675 and =
result =3D 'o';

To identify the transactions:
select distinct trans_no from trans where comp_id =3D 675 and result =3D =
'o';

To identify the transactions and the individual counts:
select trans_no, count(*) from trans where comp_id =3D 675 and result =
=3D 'o' group by trans_no;

> -----Original Message-----
> From: Richard Reina [mailto:richard@rushlogistics.com]
> Sent: Sunday, December 13, 2009 12:37 PM
> To: mysql@lists.mysql.com
> Cc: richard@rushlogistics.com
> Subject: Query help
>=20
> I was wondering if someone could lend a hand with the following query.
> I have table.
>=20
> SEARCHES
> |ID |trans_no|comp_id|result
> 13 | 455 | 675 | o
> 15 | 302 | 675 | o
> 16 | 455 | 675 | o
> 12 | 225 | 629 | y
>=20
> SELECT count(*) FROM SEARCHES WHERE comp_id=3D675 AND result=3D'o' =
GROUP BY
> trans_no;
>=20
> gives me a count of 3.
>=20
> However, what I need is a count for how many different (unique)
> transactions company number 675 got a result 'o' which would be 2 (455
> & 302). I have tried different group by columns but to no avail. Can
> someone help?
>=20
> Thanks,
>=20
> Richard
>=20
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=3Djason.trebilcock@gmail. com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Query help

On December 13, 2009 01:36:41 pm Richard Reina wrote:
> I was wondering if someone could lend a hand with the following query. I
> have table.
>
> SEARCHES
>
> |ID |trans_no|comp_id|result
>
> 13 | 455 | 675 | o
> 15 | 302 | 675 | o
> 16 | 455 | 675 | o
> 12 | 225 | 629 | y
>
> SELECT count(*) FROM SEARCHES WHERE comp_id=675 AND result='o' GROUP BY
> trans_no;
>
> gives me a count of 3.
>
> However, what I need is a count for how many different (unique)
> transactions company number 675 got a result 'o' which would be 2 (455 &
> 302). I have tried different group by columns but to no avail. Can
> someone help?
>
> Thanks,
>
> Richard
>
select count(trans_no), comp_id from SEARCHES group by comp_id

I think that should do it...haven't tested it though,

Colin
--
Living your life is a task so difficult, it has never been attempted before.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Query help

SELECT count(distinct trans_no) from SEARCHES WHERE comp_id=675 and
result='o';

----- Original Message -----
From: "Richard Reina"
To:
Cc:
Sent: Sunday, December 13, 2009 11:36 AM
Subject: Query help


>I was wondering if someone could lend a hand with the following query. I
>have table.
>
> SEARCHES
> |ID |trans_no|comp_id|result
> 13 | 455 | 675 | o
> 15 | 302 | 675 | o
> 16 | 455 | 675 | o
> 12 | 225 | 629 | y
>
> SELECT count(*) FROM SEARCHES WHERE comp_id=675 AND result='o' GROUP BY
> trans_no;
>
> gives me a count of 3.
>
> However, what I need is a count for how many different (unique)
> transactions company number 675 got a result 'o' which would be 2 (455 &
> 302). I have tried different group by columns but to no avail. Can
> someone help?
>
> Thanks,
>
> Richard
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=mysql@dawiz.net
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org