SQL nested count

Question

first: yes i'm a sql noob and i really need some help:

SELECT COUNT(id), SUM(rating), COUNT(rating) FROM comments WHERE phone='$phone';"

this needs to be something like this:

SELECT COUNT(id), SUM(rating), (COUNT(rating) WHERE rating>0) FROM comments WHERE phone='$phone';"

Can anyone help out?

Why didn't you try adding the required WHERE clause to the actual WHERE clauses? — Grant Thomas
– Grant Thomas, Commented Jun 13, 2013 at 13:05
like this: SELECT COUNT(id), SUM(rating), COUNT(rating) FROM comments WHERE phone='$phone' AND rating > 0; ?? — x4rf41
– x4rf41, Commented Jun 13, 2013 at 13:08
maybe he wants the "HAVING" clause with HAVING COUNT(rating) > 0 ... — Brewal
– Brewal, Commented Jun 13, 2013 at 13:10
I'm not sure if "HAVING" in your example only depends on rating? I need count(id) and sum(rating) in very case, but count(rating) only, if the rating is >0. — Dublay
– Dublay, Commented Jun 13, 2013 at 13:22
Something like this: SELECT COUNT(id), SUM(rating), (COUNT(rating) HAVING rating>0) FROM comments WHERE phone='$phone';" But how is the correct sql-syntax? thanks — Dublay
– Dublay, Commented Jun 13, 2013 at 13:38

MrSoundless · Accepted Answer · 2013-06-13 13:08:46Z

2

So you basically want this?

SELECT
    COUNT(id), 
    SUM(rating), 
    COUNT(rating) 
FROM comments 
WHERE phone='$phone' AND rating > 0

If not, please add some explanation to your post.

P.s. I'm guessing you're using PHP. Make sure you don't get caught in the trap that's called MySQL injection

answered Jun 13, 2013 at 13:08

MrSoundless

1,3942 gold badges12 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dublay Over a year ago

Hi, thanks for your answers: MrSoundless: I only want to count ratings, when the count is >0. Counting / SUM all other values but count rating only if it's >0.

Kickstart · Accepted Answer · 2013-06-14 09:39:11Z

0

You can use a CASE statement within the COUNT:-

$query = "SELECT COUNT(id), SUM(rating), (SUM(CASE WHEN rating > 0 THEN 1 ELSE 0 END)) 
FROM comments 
WHERE phone='$phone'";

edited Jun 14, 2013 at 9:39

answered Jun 13, 2013 at 14:14

Kickstart

21.5k2 gold badges26 silver badges33 bronze badges

7 Comments

Dublay Over a year ago

Hi Kickstart - this results in "Unexpected while". Next line is a while statement: while($row=mysql_fetch_array

Kickstart Over a year ago

Needs a semi colon at the end of the line.

Kickstart Over a year ago

Can you post the code. There seems nothing wrong with the above line so I would need to see the lines around it.

Dublay Over a year ago

Sure, actually it's like this:

$countresult=mysql_query("SELECT COUNT(id), SUM(rating), COUNT(rating) FROM comments WHERE phone='$phone';"); 					//$countresult=mysql_query("SELECT COUNT(id), SUM(rating), (SUM(CASE rating > 0 THEN 1 ELSE 0 END)) FROM comments WHERE phone='$phone';") 					while($row=mysql_fetch_array($countresult)){  					global $count; 					$count=$row[0]; 					$ratings=$row[1]; 					$sumratings=$row[2];

Kickstart Over a year ago

That should be fine, unless your commented out statement goes over multiple lines (if so the first line of the statement would be commented out but not the rest of it). Just thought of one thing though, the actual SQL statement should not be terminated by a semi colon (but this shouldn't cause the issue you are having).

|

Collectives™ on Stack Overflow

SQL nested count

2 Answers 2

1 Comment

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related