CODEWITHSUNDEEP
mysqlsqlcountnested
Dublay
Dublay

Reputation: 65

SQL nested count

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?

Upvotes: 0

Views: 1058

Answers (2)

Kickstart
Kickstart

Reputation: 21513

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'";

Upvotes: 0

MrSoundless
MrSoundless

Reputation: 1384

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

Upvotes: 2

Related Questions