Discuss Display 2 queries in the same table? in the MySQL Help forum on Dev Shed. Display 2 queries in the same table? MySQL Help forum discussing administration, SQL syntax, and other MySQL-related topics. MySQL is an open-source relational database management system (RDBMS).
Posts: 148
Time spent in forums: 18 h 39 m 9 sec
Reputation Power: 1
Quote:
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
Posts: 148
Time spent in forums: 18 h 39 m 9 sec
Reputation Power: 1
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
Posts: 7
Time spent in forums: 1 h 53 m 10 sec
Reputation Power: 0
Quote:
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.
Posts: 7
Time spent in forums: 1 h 53 m 10 sec
Reputation Power: 0
Quote:
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
Posts: 148
Time spent in forums: 18 h 39 m 9 sec
Reputation Power: 1
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?
Posts: 7
Time spent in forums: 1 h 53 m 10 sec
Reputation Power: 0
Quote:
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
Posts: 148
Time spent in forums: 18 h 39 m 9 sec
Reputation Power: 1
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");
Posts: 148
Time spent in forums: 18 h 39 m 9 sec
Reputation Power: 1
Quote:
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.
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
