I have an issue where I need to count the number of times are collective number of entries is shown.

I have one major table containing sales (Sales) which contain the core sale information. Each individual line item is an item within that sale, which is then linked to another table (items), and each line item is linked to a user found in users (users)

Sales

| ID | Item Type | User ID | 
| 1  | merch     | 1 |
| 2  | merch     | 1 |
| 3  | ticket    | 2 |
| 4  | merch     | 1 |
| 5  | ticket    | 3 |
| 6  | ticket    | 3 |

What I'm trying to do is be able to count how many people have bought X amount if items.

E.g. how can I query the above table to give me the following count data:

| noOfSales | Num |
| 1         | 1 |
| 2         | 1 |
| 3         | 1 |

I know I can query it with

SELECT count(id) FROM Sales GROUP BY user_id

but that will only return the data for each user as opposed to a count, and I need to run this on a database with 1000's of records.

CodePudding user response：

It looks like you should group sales by userid and itemtype, then group it again, on the count that resulted

SELECT ctr, count(*)
FROM
(
  SELECT count(id) as ctr FROM Sales GROUP BY user_id, item_type
) x
GROUP BY ctr