October 29th, 2012, 09:56 AM
Simple question on MySql Syntax
Hi. A bit frustrated b/c my DB skills are dusty and I'm not figuring out what i consider to be a simple statement. So I've come for help.
I have 2 tables. They each have a column called "Rating". Rating can be 0-10. What I want to do is obtain an aggregated dataset that includes:
1) Rating Category (the 0-10)
2) Count of Rating Category in Table1
3) Count of Rating in Category in Table2
Resultset would be 11 rows (the 0-10), with associated columns telling me the # of times each rating was provided in each table.
Does that make sense?
My current rendition is:
While this query "works", it doesn't give me sensible data. In fact, I'm not sure what it's giving me. Any help is appreciated.
Table1.Rating as AllRating,
count(Table1.Rating) as Tbl1Freq,
count(Table2.Rating) as Tbl2Freq
FROM Table1, Table2
Table1.Rating = Table2.Rating
GROUP BY Table1.Rating;
October 29th, 2012, 10:34 AM
because you are joining the tables, you're getting the same (inflated) counts for each COUNT()
, SUM(Tbl1Freq) AS Tbl1Freq
, SUM(Tbl2Freq) AS Tbl2Freq
FROM ( SELECT Rating
, COUNT(*) AS Tbl1Freq
, NULL AS Tbl2Freq
BY Rating ) AS data
October 29th, 2012, 11:37 AM
Thats great. Thank you. VERY much appreciated.
Originally Posted by r937