CODEWITHSUNDEEP
mysqlsql
Juan perez
Juan perez

Reputation: 13

Count items for each order

I have been stuck for a week with this SQL query.

I have the following table:

CLIENT  | DELIV. C. |
---------------------
10      | 200       |
10      | 700       |
10      | 700       |
40      | 200       |
30      | 300       |
20      | 400       |
30      | 400       |
50      | 900       |

I need to produce the following result

CLIENTS W/1 DELIV C|CLIENTS W/ 2 DELIV C|CLIENTS W/ 3 DELIV C| CLIENTS 4+ DELIV C|
----------------------------------------------------------------------------------
            3      |             1      |          1         |        0          |

Upvotes: 1

Views: 69

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

This is an aggregation on an aggregation (the second is to do the pivoting):

select sum(NumDeliveries = 1) as "CLIENTS W/1 DELIV C",
       sum(NumDeliveries = 2) as "CLIENTS W/2 DELIV C",
       sum(NumDeliveries = 3) as "CLIENTS W/3 DELIV C",
       sum(NumDeliveries > 3) as "CLIENTS 4+ DELIV C"
from (select client, count(*) as NumDeliveries
      from t
      group by client
     ) t;

In most other databases, you would write out the conditions using case as:

select sum(case when NumDeliveries = 1 then 1 else 0 end) as "CLIENTS W/1 DELIV C",

MySQL also supports this standard notation.

EDIT:

The subquery is counting the number of deliveries for each client. This is pretty basic SQL and it is what you want to pivot (that is, move from rows to columns).

The outer query is pivoting the data by using conditional aggregation. The expression:

       sum(NumDeliveries = 2) as "CLIENTS W/2 DELIV C",

is evaluated as follows:

The expression NumDeliveries = 2 returns 1 when the condition is true and false otherwise. The sum is then the sum of the number of times when this is true.

Upvotes: 2

Related Questions