CODEWITHSUNDEEP
mysqlsqljoingroup-by
Daveman426
Daveman426

Reputation: 13

My MySQL select shows only 1 result instead of all that I chose

I have a problem with this mysql exercise, could someone help me so it displays all the records I want, because now it only takes a value from the column tytul and it gives the max, min and avg from the whole table instead of separating them and then counting the max, min and avg from each tytul

SELECT 
    filmy.tytul, 
    FORMAT(MIN(wypozyczenia.data_zwrotu-wypozyczenia.data_wypozyczenia),1) AS "min",
    FORMAT(MAX(wypozyczenia.data_zwrotu-wypozyczenia.data_wypozyczenia),1) AS "max",
    FORMAT(AVG(wypozyczenia.data_zwrotu-wypozyczenia.data_wypozyczenia),1) AS "sre", 
    COUNT(wypozyczenia.id_kopii) AS "Razy" 
FROM `filmy`,`wypozyczenia`,`kopie` 
WHERE (filmy.id_filmu=kopie.id_filmu AND wypozyczenia.id_kopii=kopie.id_kopii)

Upvotes: 0

Views: 492

Answers (1)

GMB
GMB

Reputation: 222502

Basically, your query misses a GROUP BY clause at its end:

GROUP BY filmy.tytul

You should also use standard, explicit joins (with the ON keyword) rather than old-school, implicit joins (with commas in the from clause); finally, I would recommend using table aliases to shorten the query and make it more readable:

SELECT 
    f.tytul, 
    FORMAT(MIN(w.data_zwrotu-wypozyczenia.data_wypozyczenia),1) AS min_data
    FORMAT(MAX(w.data_zwrotu-wypozyczenia.data_wypozyczenia),1) AS max_data 
    FORMAT(AVG(w.data_zwrotu-wypozyczenia.data_wypozyczenia),1) AS sre, 
    COUNT(w.id_kopii) AS razy
FROM `filmy` f
INNER JOIN `kopie` k ON k.id_filmu = f.id_filmu 
INNER JOIN `wypozyczenia` w ON w.id_kopii = k.id_kopii
GROUP BY f.tytul

Please note that I removed the double quotes around column aliases; in MySQL you need to use backticks for identifiers - or better yet, use identifiers that do not conflict with SQL keywords, and hence to not need to be quoted.

Upvotes: 2

Related Questions