Simple question on MySql Syntax

Dev Shed Forums Sponsor:

October 29th, 2012, 09:56 AM

MackenzieMcDani

Registered User

Join Date: Oct 2012

Posts: 4

Time spent in forums: 26 m 46 sec
Reputation Power: 0

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:

Code:

SELECT 
Table1.Rating as AllRating, 
count(Table1.Rating) as Tbl1Freq, 
count(Table2.Rating) as Tbl2Freq
FROM Table1, Table2 
WHERE 
Table1.Rating = Table2.Rating
GROUP BY Table1.Rating;

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.

October 29th, 2012, 10:34 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,377

Time spent in forums: 3 Months 1 Week 2 Days 7 h 40 m 36 sec
Reputation Power: 4140

because you are joining the tables, you're getting the same (inflated) counts for each COUNT()

Code:

SELECT Rating
     , SUM(Tbl1Freq) AS Tbl1Freq
     , SUM(Tbl2Freq) AS Tbl2Freq
  FROM ( SELECT Rating
              , COUNT(*) AS Tbl1Freq
              , NULL     AS Tbl2Freq
           FROM Table1
         GROUP
             BY Rating  
         UNION ALL
         SELECT Rating
              , NULL     
              , COUNT(*) 
           FROM Table2     
         GROUP
             BY Rating ) AS data
GROUP
    BY Rating

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

October 29th, 2012, 11:37 AM

MackenzieMcDani

Registered User

Join Date: Oct 2012

Posts: 4

Time spent in forums: 26 m 46 sec
Reputation Power: 0

Quote:

Originally Posted by r937

because you are joining the tables, you're getting the same (inflated) counts for each COUNT()

Code:

SELECT Rating
     , SUM(Tbl1Freq) AS Tbl1Freq
     , SUM(Tbl2Freq) AS Tbl2Freq
  FROM ( SELECT Rating
              , COUNT(*) AS Tbl1Freq
              , NULL     AS Tbl2Freq
           FROM Table1
         GROUP
             BY Rating  
         UNION ALL
         SELECT Rating
              , NULL     
              , COUNT(*) 
           FROM Table2     
         GROUP
             BY Rating ) AS data
GROUP
    BY Rating

Thats great. Thank you. VERY much appreciated.

Viewing: Dev Shed Forums > Databases > MySQL Help > Simple question on MySql Syntax

« Previous Thread | Next Thread »

Developer Shed Advertisers and Affiliates

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump