CODEWITHSUNDEEP
phpmysql
Passionate Coder
Passionate Coder

Reputation: 7294

Group by with conditions

I have to display some data in php the structure of data is like this

id  channel_id  added_by

1     1          9999
2     1          195
3     8          9999
4     1          180
5     1          195
6     8          9999
7     1          9999
8     1          195
9     8          9999

I need to show total count of channel id = 1 only like this

channel_id   total  added_by

1            2           9999
1            4          -1

Its like count of all channel id which are added by 9999 and all other added are assumed as (-1)

I am able to do it like

SELECT channel_id, added_by, sum(id) as total 
from table 
where channel_id = 1 
group by channel_id  ,added_by;

but it gives following result which is not as I need

channel_id  added_by total 
1            9999      2
1             195      3
1             180      1

Upvotes: 1

Views: 92

Answers (4)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

Using CASE..WHEN expressions, you can determine a "modified" added_by value, and GROUP BY on the modified value instead.

Also, SUM(id) will not give you correct count of rows. It would rather add up the id values. You can use COUNT(*) instead to count the number of rows: 

SELECT channel_id, 
       CASE 
         WHEN added_by <> 9999 THEN -1 
         ELSE 9999 
       END AS modified_added_by, 
       COUNT(*) as total 
FROM your_table_name 
WHERE channel_id = 1 
GROUP BY channel_id, modified_added_by

Result

| channel_id | modified_added_by | total |
| ---------- | ----------------- | ----- |
| 1          | -1                | 4     |
| 1          | 9999              | 2     |

View on DB Fiddle

Upvotes: 2

Alex
Alex

Reputation: 17289

http://sqlfiddle.com/#!9/437406/2

SELECT channel_id,
       IF(added_by=9999,9999,-1) new_col,
       COUNT(*)
FROM `table`
GROUP BY channel_id, new_col

Upvotes: 1

Dharmik Raval
Dharmik Raval

Reputation: 31

Hope this will help you.

SELECT count(*) as total, channel_id, added_by from table group by channel_id, added_by;

Upvotes: 1

user2485245
user2485245

Reputation: 1

You can try this:

SELECT channel_id,
if(9999,9999,-1) as added_by,
sum(id) as total
from table
where channel_id = 1
group by channel_id,added_by;

Upvotes: 0

Related Questions