Doing a count MySQL query?

Question

I have the following table:

UID | ID  | Type
1   | 1   | product
1   | 2   | product
1   | 3   | service
1   | 4   | product
1   | 5   | product
2   | 6   | service
1   | 7   | order
2   | 8   | invoice
2   | 9   | product

I want to end up with:

UID | product | service | invoice | order
1   |  4      |  1      |  0      |  1
2   |  1      |  1      |  1      |  0

What would the SQL query look like? Or at least, the most sufficient one?

Answer 1

select count(product, service, invoice, order) from mytable

Answer 2

You're looking for something called a "pivot table", which isn't something MySQL can do natively.

You're probably best off, in this case, transforming the data in your application and using some combination of GROUP BY and/or DISTINCT to collect the data you're looking for. This query might work, I have not tested it:

SELECT Type, COUNT(ID), UID
  FROM tablename
 GROUP BY UID, Type

Answer 3

If you really only need those four types, then you can hard-code the values as follows:

select UID,
    count(case when type='product' then 1 else null end) as product,
    count(case when type='service' then 1 else null end) as service,
    count(case when type='invoice' then 1 else null end) as invoice,
    count(case when type='order' then 1 else null end) as order
from MyTable
group by UID
order by UID

Answer 4

You have to use CASE statements in MySQL to turn row data into columns (and vice versa):

  SELECT t.uid,
         SUM(CASE WHEN t.type = 'product' THEN COUNT(*) END) as PRODUCT,
         SUM(CASE WHEN t.type = 'service' THEN COUNT(*) END) as SERVICE,
         SUM(CASE WHEN t.type = 'invoice' THEN COUNT(*) END) as INVOICE,
         SUM(CASE WHEN t.type = 'order' THEN COUNT(*) END) as ORDER
    FROM TABLE t
GROUP BY t.uid, t.type

Answer 5

What you want to do is a pivot operation, which is not directly supported by SQL syntax. However, it's not too complicated, and conceptually involves 2 steps:

1. "Blow up" the data into many columns, with one row per row in the original data set. This is usually done with CASE WHEN ... ELSE ... END or occasionally with functions (like decode() in oracle). I'll use CASE WHEN in the example below, since it works equally well for most RDBMSes
2. Use GROUP BY and aggregate functions (SUM, MIN, MAX, etc) to collapse the many rows into the output row set you want.

I'm using this data set for the example:

mysql> select * from foo;
+------+------+---------+
| uid  | id   | type    |
+------+------+---------+
|    1 |    1 | product | 
|    1 |    2 | product | 
|    1 |    3 | service | 
|    1 |    4 | product | 
|    1 |    5 | product | 
|    2 |    6 | service | 
|    1 |    7 | order   | 
|    2 |    8 | invoice | 
|    2 |    9 | product | 
+------+------+---------+

Step 1 is to "blow up" the data set:

select uid
     , case when type = 'product' then 1 else 0 end as is_product
     , case when type = 'service' then 1 else 0 end as is_service
     , case when type = 'invoice' then 1 else 0 end as is_invoice
     , case when type = 'order' then 1 else 0 end as is_order
  from foo;

which gives:

+------+------------+------------+------------+----------+
| uid  | is_product | is_service | is_invoice | is_order |
+------+------------+------------+------------+----------+
|    1 |          1 |          0 |          0 |        0 | 
|    1 |          1 |          0 |          0 |        0 | 
|    1 |          0 |          1 |          0 |        0 | 
|    1 |          1 |          0 |          0 |        0 | 
|    1 |          1 |          0 |          0 |        0 | 
|    2 |          0 |          1 |          0 |        0 | 
|    1 |          0 |          0 |          0 |        1 | 
|    2 |          0 |          0 |          1 |        0 | 
|    2 |          1 |          0 |          0 |        0 | 
+------+------------+------------+------------+----------+

Next we collapse to one row in the output per date, and sum each of the is_* columns, using or initial query as an inline view (aka a "subquery"):

select uid
     , sum(is_product) as count_product
     , sum(is_service) as count_service
     , sum(is_invoice) as count_invoice
     , sum(is_order)   as count_order
  from (
         select uid
              , case when type = 'product' then 1 else 0 end as is_product
              , case when type = 'service' then 1 else 0 end as is_service
              , case when type = 'invoice' then 1 else 0 end as is_invoice
              , case when type = 'order' then 1 else 0 end as is_order
           from foo
       ) x
 group by uid;

(Note also that you can collapse these two queries into one, though I've shown them separately here for clarity; In MySQL at least, this seems to result in a simpler execution plan, which often means faster execution -- as always, test your SQL performance on realistic data sets, don't take my word for it!)

This gives us:

+------+---------------+---------------+---------------+-------------+
| uid  | count_product | count_service | count_invoice | count_order |
+------+---------------+---------------+---------------+-------------+
|    1 |             4 |             1 |             0 |           1 | 
|    2 |             1 |             1 |             1 |           0 | 
+------+---------------+---------------+---------------+-------------+

Which is the desired result.