So I need to get the count for the number items from found in different user accounts. Basically, the items are stored in lists, which are then stored in accounts. The issue is that the items have a list_id, but no account_id even though lists have an account_id. If there was also an account_id for items, it would be easy, but it goes more like this:
account -> list -> item
So there's no direct connexion between account and item. I know this is not optimal, but I'm not the one who designed the database. I just have to work with it.
Here's how the data is structured:
account (Where the account_id comes from)
----------------------------------
| account_id | account_name| ... |
|------------|-------------|-----|
| 1 | account_1 | ... |
| 2 | account_2 | ... |
| 3 | account_3 | ... |
| 4 | account_4 | ... |
| 5 | account_5 | ... |
| ... | ... | ... |
----------------------------------
list (Where the list_id comes from. Also linked to the account table by the account_id FOREIGN KEY)
------------------------------------------
| list_id | list_name | account_id | ... |
|---------|-----------|------------| ... |
| 1 | list_1 | 1 | ... |
| 2 | list_2 | 1 | ... |
| 3 | list_3 | 2 | ... |
| 4 | list_4 | 2 | ... |
| 5 | list_5 | 3 | ... |
| ... | ... | ... | ... |
------------------------------------------
item (Where the items come from. Also linked to the list table by the list_id FOREIGN KEY)
---------------------------------------
| item_id | item_name | list_id | ... |
|---------|-----------|---------|-----|
| 1 | item_1 | 1 | ... |
| 2 | item_2 | 1 | ... |
| 3 | item_3 | 2 | ... |
| 4 | item_4 | 2 | ... |
| 5 | item_5 | 3 | ... |
| ... | ... | ... | ... |
---------------------------------------
What I manage to get (I've added the list_id even though it's uncessary to show you that there is an
item_qty count for each list)
----------------------------------
| account_id | list_id| item_qty |
|------------|--------|----------|
| 1 | 1 | 6 |
| 1 | 2 | 1 |
| 1 | 3 | 5 |
| 2 | 4 | 2 |
| 2 | 5 | 2 |
----------------------------------
What the desired result is.
-------------------------------
| account_id | item_qty | ... |
|------------|----------|------
| 1 | 12 | ... |
| 2 | 4 | ... |
| ... | ... | ... |
-------------------------------
Here is the code that gets me the current results:
SELECT list.account_id, COUNT(*) AS item_qty
FROM item
JOIN list ON list.list_id = item.list_id
GROUP BY items.list_id
Here's what I've tried to get the desired results:
SELECT * FROM (
SELECT list.account_id as id, COUNT(*) AS item_qty
FROM item
JOIN list ON list.list_id = item.list_id
GROUP BY items.list_id ) as t
GROUP BY id
However, when I do this, all I get is the count from one of the lists. So if list 1 from account 1 has 3 items, list 2 from account 1 has 4 items, I get a count of 3 even if I group the lists by account.
What I need is to get the count of all items within a list, then the count of all items within all list of an account.
As far as concerns, your current attempt is almost there. You just need to change the group by clause to match the non-aggregated column in the select clause:
SELECT list.account_id, COUNT(*) AS item_qty
FROM item
JOIN list ON list.list_id = item.list_id
GROUP BY list.account_id -- instead of items.list_id