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

    Join Date
    Mar 2013
    Posts
    22
    Rep Power
    0

    Need help with COUNT in subquery


    Hi,

    I have table which holds users week schedule. Each day of the week is inserted as a row for each week for each user and each day has 5 parts.

    So every user has a total of 35 entries for each week. What i want to find which users in the db have more than 35 entries for a week because there is somewhere a fault in the system that creates more entries and i need to fix those users.

    So basically i need to search the DB and find which of the users in that table have more than 35 entries for each week.


    The DB table is like this :

    WEEKLY_FOOTBALL
    ***********
    weekly_id
    weekly_football_user_id
    weekly_football_day
    weekly_football_week_number
    weekly_football_text

    USERS
    ***********
    user_id
    user_name
    user_surname


    I tried the following but doesnt work :

    SELECT
    weekly_football_user_id,(SELECT COUNT(weekly_football_week_number) AS rows FROM weekly_football) AS total

    FROM `weekly_football`

    WHERE total.rows > 35

    GROUP BY weekly_football_week_number



    i get "#1054 - Unknown column 'total.rows' in 'where clause'".




    The following gives me correctly the data but only for 1 user :

    SELECT
    weekly_football_user_id,
    weekly_football_week_number,
    COUNT(weekly_football_week_number)

    FROM `weekly_football`

    WHERE weekly_football_user_id = 209

    GROUP BY weekly_football_week_number



    but if i remove "WHERE weekly_football_user_id = 209" it will display sumed up data. How can i do it to check for all users in one query?


    Any help appreciated,

    Thank you
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    22
    Rep Power
    0
    I came up with this :

    SELECT

    user_id,
    user_name,
    weekly_football_week_number,(SELECT COUNT(weekly_football_week_number)

    FROM `weekly_football` ,users

    WHERE weekly_football_user_id = user_id

    AND weekly_football_week_number = 10

    GROUP BY weekly_football_week_number) as lol

    FROM `weekly_football` ,users

    WHERE weekly_football_user_id = users.user_id



    but the results are wrong..
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,147
    Rep Power
    4274
    seems like you're over-thinking it

    Code:
    SELECT weekly_football_user_id
         , weekly_football_week_number
         , COUNT(*)
      FROM weekly_football 
    GROUP 
        BY weekly_football_user_id
         , weekly_football_week_number
    HAVING COUNT(*) > 35
    by the way, embedding the table name at the front of all the column names in that table is unnecessary and extraneous noise
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    22
    Rep Power
    0
    oh boy, i feel stupid

    works great, thanks once again!

    i have 3 questions, if you have time and want, you may reply!

    a) i see that you use HAVING a lot. Is it the same as "WHERE"? Is it better?

    b) regarding tablename, i thought it was mandatory to use it when querying two tables at the same tame in order to avoid possible column name duplication, is that wrong?

    c) when using two GROUP BY , it will group the initial results with the first and then the new results will be groupped by the second?

    thanks again!
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,147
    Rep Power
    4274
    Originally Posted by BlazeMike
    a) i see that you use HAVING a lot. Is it the same as "WHERE"?
    no... similar, but ~not~ the same

    one is used to filter rows going into the FROM clause, while the other is used to filter rows coming out of the GROUP BY clause

    Originally Posted by BlazeMike
    b) regarding tablename, i thought it was mandatory to use it when querying two tables at the same tame in order to avoid possible column name duplication, is that wrong?
    yup, wrong

    you would use table qualifiers and column aliases

    Originally Posted by BlazeMike
    c) when using two GROUP BY , it will group the initial results with the first and then the new results will be groupped by the second?
    not really

    the GROUP BY clause produces one row for every distinct combination of values in all columns mentioned
    Last edited by r937; March 27th, 2013 at 02:57 PM.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo