Skip to main content
Inspiring
May 28, 2008
Question

Query question - how to do this

  • May 28, 2008
  • 4 replies
  • 672 views
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.
    This topic has been closed for replies.

    4 replies

    Participating Frequently
    May 29, 2008
    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
    Participating Frequently
    May 28, 2008
    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
    Participating Frequently
    May 31, 2008
    This works great.
    Inspiring
    May 28, 2008
    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.
    Prasanth_Kumar_S
    Inspiring
    May 28, 2008
    Hi,
    If you are using SQL 2005, you can try using the Crosstab queries. Crosstab queries.

    - Prasanth