Given the sample table of:
---- ---------- --------- ------
| id | userName | storeId | cost |
---- ---------- --------- ------
| 1 | foo | 1 | 10 |
| 2 | bar | 1 | 10 |
| 3 | baz | 5 | 5 |
| 4 | baz | 3 | 20 |
| 5 | qux | 1 | 5 |
| 6 | qux | 4 | 20 |
| 7 | qux | 15 | 30 |
| 8 | qux | 17 | 40 |
| 9 | qux | 3 | 5 |
| 10 | quux | 6 | 20 |
---- ---------- --------- ------
I would like to work out how many people purchased at each store and how many did not. I want the report to display the results grouped by store.
I know the statement select storeId, count(distinct username) as total from purchases group by storeId provides me with how many people purchased in each store, but I want to subtract the result of the query select count(distinct userName) from purchases; in another column. I would expect the sample output to display as follows.
--------- ----------- --------------
| storeId | purchased | notPurchased |
--------- ----------- --------------
| 1 | 3 | 2 |
| 3 | 2 | 3 |
| 4 | 1 | 4 |
| 5 | 1 | 4 |
| 6 | 1 | 4 |
| 15 | 1 | 4 |
| 17 | 1 | 4 |
--------- ----------- --------------
CodePudding user response：
You can use NOT condition with IN() function
CodePudding user response：
As long a subse3lect gives back only only one, you can use following
SELECT storeId, COUNT(DISTINCT username) AS total, ((SELECT COUNT(DISTINCT userName) FROM purchases) - COUNT(DISTINCT username)) notPurchased FROM purchases GROUP BY storeIdstoreId | total | notPurchased ------: | ----: | -----------: 1 | 3 | 2 3 | 2 | 3 4 | 1 | 4 5 | 1 | 4 6 | 1 | 4 15 | 1 | 4 17 | 1 | 4
db<>fiddle here