How can I get Counts on Multiple tables? in SQL

Question

SELECT `p2`, COUNT(*) FROM dsf_2022_raw_new WHERE `p2` LIKE 'BA' 

p2   COUNT(*)
BA    608

I was able to get count from that table but I need to get "BA" from 4 more tables with similar structure.

Then what if I want to select BA, DL, TS, AL these are actually first two characters of the post code.

UNION ALL the tables. Or, if you really need separate tables, create a view that does UNION ALL. — jarlh
– jarlh, Commented Oct 5, 2022 at 8:36

mowgle · Accepted Answer · 2022-10-05 09:47:49Z

1

welcome to Stack Overflow. So, looks like you're asking two questions there.

Regarding getting the count from four separate tables, I suggest running the same SQL query you've written on these four tables, saving each query output to a single view, and then running a group-by query to calculate the total count. Something like

SELECT count(*), p2 from <your_view> group by p2

Selecting queries from multiple postcodes can be achieved using OR statements with your where clause. So, taking your original query,

SELECT p2, COUNT(*) FROM dsf_2022_raw_new 
WHERE p2 LIKE 'BA' 
OR WHERE p2 LIKE 'DL'...

Hope this helps. Happy coding

answered Oct 5, 2022 at 9:47

mowgle

135 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1