Combining the separate queries from multiple tables

Question

Table1

ID     | First     |   Last   | Branch
111       Amy           A         10
222       Yuki          B         11
333       Smith         C         10
444       David         D         12
555       Mike          E         13

Table2

Branch     | Food     |   State
10           Burger        MI
11           Taco          CA
99           Taco          CA
13           Taco          CA
12           Burger        MI

Table3

Food      | State      | Rating
Burger        MI           bad
Taco          CA           bad
Steak         TX           good

QUESTION: How to make the table below:

Rating  | CountState   | CountID
Bad          2               5
Good         1               0

I am a novice to SQL, so I tried my best to deliver my thoughts into this question. I am thinking of UNION ALL of two queries but failed. Any idea is appreciated. Thank you very much.

SELECT a.Rating, COUNT(a.Rating) AS CountState
FROM Table1 a
GROUP BY a.Rating
ORDER BY a.Rating

You have both python and SQL listed, which one are you actually using to do the counts? — Chris, Jun 29 '21 at 20:14
1) pls describe the logic to calculate countid 2) pls share what you have tried so far — Shadow, Jun 29 '21 at 20:17
Your query in your question refers to table1.rating which you show in table3 so this makes no sense. — Stu, Jun 29 '21 at 20:44

score 0 · Answer 1 · answered Jun 29 '21 at 20:43

Looking at your data and what you require for your sample output, it appears you just need a count of Table1 IDs and a distinct count of Table3 States - if that's not correct you need to clarify your relationships and the data better.

The following should give you your desired results

select t3.rating,
    count(distinct t3.state) CountState, 
    Count(t1.Id) CountId
from t3 
left join t2 on t2.state=t3.state
left join t1 on t1.branch=t2.branch
group by t3.rating

It works now. Thank you very much. May I ask the theory behind this line? < count(distinct t3.state) CountState,> — user12909472, Jun 30 '21 at 01:26

Combining the separate queries from multiple tables

1 Answers1