Not a complicated query, though having a small problem with the below snippet.
This query returns a list of reviews based on a certain name, works good:
SELECT comments, publisher, rating
FROM `tbl_reviews` WHERE name='name123'
While this query only returns 1 review, after I added the AVG function:
SELECT comments, publisher, rating,
ROUND(AVG(rating), 1) AS ratingAVG
FROM `tbl_reviews` WHERE name='name123'
In short, after I added ROUND(AVG(rating), 1) AS ratingAVG the average is calculated but only one review is returned, though in my first snippet, all reviews are returned.
Can anybody help, know what I’m doing wrong?
Do I need a separate query for this?
Thanks Mikey
GROUP BY could be the solution, though I’ve just tried this and the result is the same, only one row.
Ok, as things stand I’m returning about 10/20 rows depending on who’s name I select, each name has a number of reviews about that person. I’ve minimised below for viewing, comments would also be shown.
+-------+---------+--------+
|review_id | name | rating |
+----------+------+--------+
|1 | Jack | 1 |
|2 | Jack | 4 |
|3 | Jack | 1 |
|4 | Jack | 2 |
|24 | Jack | 4 |
+----------+------+--------+
As well as returning all the reviews for that person, I also need to show the overall average of that persons rating.
[quote=“computerbarry, post:4, topic:147227, full:true”]GROUP BY could be the solution, though I’ve just tried this and the result is the same, only one row.[/quote]then you’re doing it wrong
any chance you could show the query with the GROUP BY?
showing detail ~plus~ aggregates usually means a UNION is necessary
Ok, I’m running this through cpanel sql query window, again, only returns one result:
SELECT name, comments, publisher, rating,
ROUND(AVG(rating), 1) AS ratingAVG
FROM `tbl_reviews` WHERE `name`='Jack'
GROUP BY `name`;
-----+-------------------+-----------+--------+-----------+
name |comments | publisher | rating | ratingAVG |
-----+-------------------+-----------+--------+-----------+
Jack |Some comments here | Mr H | 5 | 3.7 |
-----+-------------------+-----------+--------+-----------+
Thinking about it, I only need the value ONCE for the overall rating, not repeated for every review/row.
Also note: the ratingAVG is being calculated from 20+ reviews/comments as shown in #4.
just a few comments/questions before i tackle the query for you
are you always going to pull results for only a specific person? because if you write WHERE name = 'Jack' then you don’t really need to have name in the SELECT clause
also, think about how GROUP BY works – whatever column(s) you put into the GROUP BY clause, you’re going to get only one row for each separate value, so putting name into the GROUP BY means you get only one row for each name, and since you’re only going after a single name, it makes sense that the entire query returns only one row
anyhow, try this –
SELECT comments
, publisher
, rating
FROM reviews
WHERE name = 'Jack'
UNION ALL
SELECT '----'
, '----'
, ROUND(AVG(rating),1)
FROM reviews
WHERE name = 'Jack'
notice there’s no “tbl_” prefix on my table name
just like there’s no “col_” prefix on my column names
[quote=“mikey_w, post:7, topic:147227”]
I think you need two queries, because you are asking for two different things.
[/quote] This is what I have already, as mentioned yesterday.
[quote=“mikey_w, post:7, topic:147227”]
You could write a query that returns all Reviews for Mary with Mary’s AVG repeating, but why?
[/quote] My point exactly!
[quote=“r937, post:8, topic:147227”]
are you always going to pull results for only a specific person?
[/quote] Yes, each page these reviews are showing has a $_GET for the name.
[quote=“r937, post:8, topic:147227”]
you don’t really need to have name in the SELECT clause
[/quote] I kind of only put that there for easier viewing for the post, in my live code this is not included.
[quote=“r937, post:8, topic:147227”]
it makes sense that the entire query returns only one row
[/quote] And the row I need to return is the AVG of rating.
In a nutshell, I’m already showing the reviews as a loop based on whatever name/page is showing, I just need a simple way to calculate the AVG rating. As Mike mentioned, create another SELECT, which I already have.
Im now wondering will you code improve/be better than two separate SELECT?
[quote=“r937, post:8, topic:147227”]
one of my pet peeves
[/quote] haha I did think by using tbl_ I was showing that I was a more advanced coder
[quote=“computerbarry, post:10, topic:147227”]Im now wondering will you code improve/be better than two separate SELECT?[/quote]i’m pretty sure it will
a UNION query can have only one ORDER BY, and it goes at the end – it controls sorting all rows from all SELECTs in the union
so LIMIT 1 is inappropriate
put your ORDER BY at the end, and see where the ‘----’ row ends up – if it’s not last, then let me know and i’ll give you a different solution, although you will have to show your exact query
I think this was the problem, I was also getting inconsistent results, though after further testing, I think I have it! Everythings working now.
Nice to know thanks.
Ok, here is the full script I’m using:
SELECT comments
, DATE_FORMAT(review_date, '%M %D %Y') AS subdir
, publisher
, rating
FROM tbl_reviews
WHERE name = 'jack' AND confirmed=1
UNION ALL
SELECT '----'
, '----'
, '----'
, ROUND(AVG(rating),1) AS ratingAVG
FROM tbl_reviews
WHERE name = 'jack' ORDER BY subdir DESC
The output
+------------------+-----------------+-----------+--------+
comments |subdir | publisher | rating |
+------------------+-----------------+-----------+--------+
Some comments here |January 1st 2015 | Mr H | 5.0 |
+------------------+-----------------+-----------+--------+
Some comments here |January 2nd 2015 | Mr H | 4.0 |
+------------------+-----------------+-----------+--------+
Some comments here |January 3rd 2015 | Mr H | 1.0 |
+------------------+-----------------+-----------+--------+
Some comments here |January 4th 2015 | Mr H | 5.0 |
+------------------+-----------------+-----------+--------+
---- |---- |---- | 3.7 |
+------------------+-----------------+-----------+--------+
So what do you you think?
Looking quite good, can you see any issues with this r937?
looks fine, except you’re going to have users asking you to sort the dates into actual date order rather than this – April, August, December, February, January, July, June, March, May, November, October, September
plus, you should actually have all WHERE conditions in both SELECTs – confirmed=1 right now appears only in the first one, so the average may be incorrect
This was one issue I was having. Previously, before we added the UNION I was sorting the data by review_date (2015-04-06), though now I need to use subdir because it was saying:
Unknown column review_date in order clause
Is there a way to sort by review_date and not subdir?
Yes, good call, I was thinking about this, didn’t know if I needed to add it to both select statements. Thanks for confirming.
Another question, how many SELECT’s can we have in a UNION?
Is this commonly done, what if we added another 3 or 4 into the mix, or is this not recommended, best practice?
you can have as many SELECTs as you want although some database systems might have a limit
as far as your date problem goes, you can still sort by date and display a formatted date, you just have to use two columns
but now the ‘----’ value will be incompatible with a date, and perhaps mess up the sequence of the final average row, so let’s pull out that additional trick i mentioned a while ago…
SELECT comments
, review_date
, DATE_FORMAT(review_date,'%M %D %Y') AS subdir
, publisher
, rating
, 1 AS sort_column
FROM tbl_reviews
WHERE name = 'jack'
AND confirmed=1
UNION ALL
SELECT 'AVERAGE'
, NULL
, NULL
, NULL
, ROUND(AVG(rating),1) AS ratingAVG
, 2
FROM tbl_reviews
WHERE name = 'jack'
AND confirmed=1
ORDER
BY sort_column
, review_date DESC
here we take control and make sure the average row comes last
i occasionally write ‘humpty’ and ‘dumpty’ instead of 1 and 2 for the sort_column values, but i think that might just be a bit too confusing
