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

    Join Date
    Sep 2012
    Posts
    204
    Rep Power
    2

    Display 2 queries in the same table?


    I have 2 Queries. but i want to show them in the same table. is it possible to iterate through them at the same time? pretty confused here!

    --------------------------------------------------- |
    category_name (query1) | average score (query1) |
    -----------------------------------------------------
    number of empty_questions (query2) |
    -----------------------------------------------------

    --------------------------------------------------- |
    category_name (query1) | average score (query1) |
    -----------------------------------------------------
    number of empty_questions (query2) |
    -----------------------------------------------------

    Thanks!
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,172
    Rep Power
    4274
    Originally Posted by notflip
    pretty confused here!
    me too

    are you sure you're not mixing up two separate processes?

    1. retrieve data in simplest and most efficient manner

    2. use application language to reformat query results
    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
    Originally Posted by r937
    me too

    are you sure you're not mixing up two separate processes?

    1. retrieve data in simplest and most efficient manner

    2. use application language to reformat query results
    How do i know what the most efficient manner is ?
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,172
    Rep Power
    4274
    Originally Posted by notflip
    How do i know what the most efficient manner is ?
    simple queries, no cosmetic restructuring, properly optimized with indexes, table joins as appropriate
    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
    Originally Posted by r937
    simple queries, no cosmetic restructuring, properly optimized with indexes, table joins as appropriate
    I know what you mean. But im very new to MySql. so right now it's finding your way and being happy when it' works I guess.. In the end I also want perfection and good db-performance
  10. #6
  11. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    204
    Rep Power
    2
    I'm nearly there! Do you have ANY clue on how to solve this one... Subqueries freak me out for now

    I can't seem to get a WHERE clause inside this one? The line in bold has to be higher then 0 to be counted but

    WHERE antwoord_naam > 0 can't be inserted because it's already a subquery?

    Code:
    SELECT 
         sum(case when antwoord_naam = 0 then 1 else 0 end) as niet_ingevuld,
    COUNT(antwoorden.vraag_id) as aantal,
         ROUND(AVG(antwoord_naam),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 antwoorden.werknemer_id = 14
    GROUP BY categorieen.categorie_id
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    7
    Rep Power
    0
    Originally Posted by notflip
    The line in bold has to be higher then 0 to be counted
    To only return those results which have average > 0 try something like this at the end...

    Code:
    HAVING average > 0
    FYI - A HAVING clause acts like a filter on the results returned from the query above it. So the entire result is essentially created and then passed to your HAVING clause which filters out those rows which having average less than 0.
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    7
    Rep Power
    0
    Originally Posted by notflip
    I have 2 Queries. but i want to show them in the same table. is it possible to iterate through them at the same time? pretty confused here!
    A UNION can join results from 2 individual queries, as long as each query returns the same column names and column count.

    eg. (untested) ...

    Code:
    SELECT firstname, lastname FROM table1
    UNION
    SELECT firstname, lastname FROM table2
    UNION
    SELECT t3.fn as firstname, t3.ln as lastname FROM table3 as t3
    As an example, this wouldn't work because the result set from each return a different number of columns:

    Code:
    SELECT firstname, middlename, lastname FROM table1
    UNION
    SELECT firstname, lastname FROM table2

    Comments on this post

    • r937 agrees : thanks for taking the time to give examples! very helpful for new developers
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,172
    Rep Power
    4274
    Originally Posted by regany
    ... as long as each query returns the same column names and column count.
    this is correct, except for the part about the same column names

    the columns of all subselects in a union query must have

    1. same number of columns
    2. same datatype, vertically

    the column names for the result table produced by a union query are taken only from the first subselect

    for example...

    SELECT a,b,c FROM table1
    UNION
    SELECT foo,bar,fap FROM jabberwocky
    UNION
    SELECT 1,2,3

    the result set will have column names a,b,c
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  18. #10
  19. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    204
    Rep Power
    2
    When i do it with the having clause at the end, The output is wrong.. It should check for the Condition inside the Subquery in the beginning. is this possible?
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    >the columns of all subselects in a union query must have

    >1. same number of columns
    >2. same datatype, vertically

    MySQL will forgive a rule 2 infringement - although I'm not sure why you'd want to mix columns of differing data types in a resultset!
  22. #12
  23. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    7
    Rep Power
    0
    Originally Posted by notflip
    When i do it with the having clause at the end, The output is wrong.. It should check for the Condition inside the Subquery in the beginning. is this possible?
    Not really sure what you mean by the output is wrong... you could throw a MAX in there so that clause will either return 0 or anything greater than 0 for the average, if that's what you're trying to do...

    Code:
    MAX((ROUND(AVG(antwoord_naam),1) * 10),0) as average
  24. #13
  25. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    204
    Rep Power
    2
    hmh. Almost there! Damn this is so complicated! I know what the problem is..

    The part in bold. Has to exclude every 0 value in the average. So when it's 0, it can't be added to the average

    Code:
    SELECT 
         SUM(case when antwoord_naam = 0 then 1 else 0 end) as niet_ingevuld,
         COUNT(antwoorden.vraag_id) as aantal,
         ROUND(AVG(antwoord_naam),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 antwoorden.werknemer_id = '$werknemerID'
    GROUP BY categorieen.categorie_id");
  26. #14
  27. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    204
    Rep Power
    2
    Originally Posted by notflip
    hmh. Almost there! Damn this is so complicated! I know what the problem is..

    The part in bold. Has to exclude every 0 value in the average. So when it's 0, it can't be added to the average

    Code:
    SELECT 
         SUM(case when antwoord_naam = 0 then 1 else 0 end) as niet_ingevuld,
         COUNT(antwoorden.vraag_id) as aantal,
         ROUND(AVG(antwoord_naam),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 antwoorden.werknemer_id = '$werknemerID'
    GROUP BY categorieen.categorie_id");
    Code:
    SELECT 
         SUM(case when antwoord_naam = 0 then 1 else 0 end) as niet_ingevuld,
         COUNT(antwoorden.vraag_id) as aantal,
         ( SELECT AVG(NULLIF(antwoord_naam ,0)) * 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 antwoorden.werknemer_id = '$werknemerID'
    GROUP BY categorieen.categorie_id");
    This is the solution! Thank you very much! Didn't know the function!
    Last edited by notflip; November 7th, 2012 at 05:17 AM.

IMN logo majestic logo threadwatch logo seochat tools logo