Sumsar
Sumsar

Reputation: 134

MySQL Couting duplicates of duplicates

I have a database filled with orders, with consist of an id and a customerName. What i'm trying to do is, finding how many of different order sizes is made. If a customer makes multiple orders will multiple rows containing the same customerName

I would like an output which looks somewhat like this:

+------------+----------+
| size       | quantity |
+------------+----------+
| 1          |      321 |
| 2          |      148 |
| 3          |       98 |
| 4          |       63 |
| 5          |       22 |
| 6          |        3 |
+------------+----------+

Edit 1:

+------------------+-----------------+------+-----+---------+----------------+
| Field            | Type            | Null | Key | Default | Extra          |
+------------------+-----------------+------+-----+---------+----------------+
| id               | int(6) unsigned | NO   | PRI | NULL    | auto_increment |
| signupDate       | date            | YES  |     | NULL    |                |
| customerName     | varchar(128)    | YES  |     | NULL    |                |
+------------------+-----------------+------+-----+---------+----------------+

Upvotes: 0

Views: 44

Answers (2)

lei yu
lei yu

Reputation: 58

select 
size,count(1)
from
(
select customerName,count(1) as size from table group by customerName
) tmp group by  size

if this what you want?

Upvotes: 3

asmgx
asmgx

Reputation: 8054

I assume you have another table for orders that shows the size and quantity

ID, OrderNo, Size, Qty

in that case this query may help

select Size, SUM(Qty) as Qty 
From Orders
GROUP BY Size

Upvotes: 0

Related Questions