#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    204
    Rep Power
    2

    Stuck on a Query ( Need a superhero here )


    Hi. I'm stuck on a Query.

    This query checks antwoorden, checks if they are filled in with the SUM case when is 0.. the only problem is.. that the "categorieen.categorie_naam" .. it doesn't show the right categories for this person with ID 42

    This query took me 10 hours.. and i'm so stuck in here. I don't even know if it's written well?

    Database screenshot


    Code:
    SELECT 
    SUM(case when antwoord_naam = 0 then 1 else 0 end) as niet_ingevuld,
    COUNT(antwoorden.vraag_id) as aantal,
    (SELECT ROUND(AVG(NULLIF(antwoord_naam ,0)),1) * 10) as average, 
    categorieen.categorie_naam
    FROM antwoorden
    INNER JOIN vragen ON antwoorden.vraag_id = vragen.vraag_id
    INNER JOIN categorieen ON categorieen.categorie_id = vragen.categorie_id
    WHERE werknemer_id = 42
    GROUP BY categorieen.categorie_id ORDER BY categorie_naam
    Last edited by notflip; February 7th, 2013 at 06:02 AM.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,163
    Rep Power
    4274
    Originally Posted by notflip
    it doesn't show the right categories for this person with ID 42
    i'll bet that it does

    put both the id and the name into the SELECT clause

    also, remove the unnecessary SELECT for the average
    Code:
    SELECT SUM(CASE WHEN antwoorden.antwoord_naam = 0 
                    THEN 1 ELSE 0 END) AS niet_ingevuld
         , COUNT(antwoorden.vraag_id) AS aantal
         , ROUND(AVG(NULLIF(antwoorden.antwoord_naam,0)),1) * 10 AS average
         , categorieen.categorie_id 
         , categorieen.categorie_naam
      FROM antwoorden
    INNER 
      JOIN vragen 
        ON vragen.vraag_id = antwoorden.vraag_id
    INNER 
      JOIN categorieen 
        ON categorieen.categorie_id = vragen.categorie_id
     WHERE antwoorden.werknemer_id = 42
    GROUP 
        BY categorieen.categorie_id 
    ORDER 
        BY categorieen.categorie_naam
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    204
    Rep Power
    2
    Thank you for the fast response.. weird thing is. it's still not showing the right categories.. they're from another 'lijst'..

    it should show these categories for this user..

  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,163
    Rep Power
    4274
    well i'm sorry to disagree with you but the query as written is correct, because it follows the foreign keys properly

    the categories that the query will return ~are~ the ones related to that particular antwoorden row

    perhaps you should inspect your data, specifically the foreign key values, closely
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    204
    Rep Power
    2
    I'll take a look at them! The database is also poorly designer.. i was almost there but now the problems show up thanks for the response. I'll dive into mysql later. first i need to try and make this work!

IMN logo majestic logo threadwatch logo seochat tools logo