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 = 675:
select count(distinct trans_no) from trans where comp_id = 675 and result = 'o';
To identify the transactions:
select distinct trans_no from trans where comp_id = 675 and result = 'o';
To identify the transactions and the individual counts:
select trans_no, count(*) from trans where comp_id = 675 and result = 'o' group by
trans_no;
> -----Original Message-----
> From: Richard Reina [mailto:richard@stripped]
> Sent: Sunday, December 13, 2009 12:37 PM
> To: mysql@stripped
> Cc: richard@stripped
> 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=1
