#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    4
    Rep 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.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,235
    Rep Power
    4279
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    4
    Rep Power
    0
    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.

IMN logo majestic logo threadwatch logo seochat tools logo