Query question - how to do this

[trojnfn]

I have a table that contains a supplier_number, and a
status_flag (y or n). There can be multiple occurances
of each supplier number.

For my report, I need to show (count) the number of
occurances of each supplier_number, and then count the
total number of status flag Y and count the total
number of status_flag N for each supplier.

I use this query :
select supplier_number, status_flag, count(*) as cnt
from table
group by supplier_number, status_flag
order by supplier_number,status_flag

However, this gives me multiple rows per supplier number, one row for the Yes count and another row
for the N count.

But I need the report output to have one row per supplier, listing the three columns (supplier number, total yes count, total no count)

How do I write the query for this ?

Thanks for any help.

[Prasanth_Kumar_S]

Hi,
If you are using SQL 2005, you can try using the Crosstab queries. Crosstab queries.

- Prasanth

[Dan_Bracuk]

Your query is fine. cfoutput has a group attribute that will do exactly what you say you are trying to accomplish. Details are in the cfml reference manual.

[paross1]

How about this?

SELECT t.supplier_number,
COUNT(*) AS supplier_count,
(SELECT COUNT(*)
FROM table t1
WHERE status_flag = 'y'
AND t.supplier_number = t1.supplier_number) AS yes_count,
(SELECT COUNT(*)
FROM table t1
WHERE status_flag = 'n'
AND t.supplier_number = t1.supplier_number) AS no_count
FROM table t
GROUP BY t.supplier_number
ORDER BY t.supplier_number

(EDIT: forgot to include the supplier count the first time)

Phil

[Trojan_Fan]

This works great.

[Etienne]

An other solution :

select supplier_number,
count(*) as cnt,
SUM( IF( status_flag = 'y', 1, 0 ) ) as yes_cnt,
SUM( FI( status_flag = 'n', 1, 0 ) ) as no_cnt
from table
group by supplier_number
order by supplier_number

IF is a MySQL function. With Oracle you can use DECODE (or CASE).

Etienne