Count with IF condition in MySQL query

by

Use sum() in place of count()

Try below:

SELECT
    ccc_news . * , 
    SUM(if(ccc_news_comments.id = 'approved', 1, 0)) AS comments
FROM
    ccc_news
    LEFT JOIN
        ccc_news_comments
    ON
        ccc_news_comments.news_id = ccc_news.news_id
WHERE
    `ccc_news`.`category` = 'news_layer2'
    AND `ccc_news`.`status` = 'Active'
GROUP BY
    ccc_news.news_id
ORDER BY
    ccc_news.set_order ASC
LIMIT 20

More Related Contents:

  1. MySql Count cannot show 0 values
  2. left join query keep a column empty
  3. Complex Count in MySQL
  4. How can I select data from different tables?
  5. Join between tables in two different databases?
  6. MySQL JOIN ON vs USING?
  7. Required to join 2 tables with their FKs in a 3rd table
  8. How to use mysql JOIN without ON condition?
  9. CROSS/OUTER APPLY in MySQL
  10. Most efficient way to get table row count
  11. How to select from two tables in MySQL even if not all rows in one table have corespondents in the other?
  12. When to use STRAIGHT_JOIN with MySQL
  13. MySQL JOIN the most recent row only?
  14. mysql: get record count between two date-time
  15. mysql query show multiple tables from one ID column
  16. How to find non-existing data from another Table by JOIN?
  17. LEFT JOIN only first row
  18. COUNT(*) from multiple tables in MySQL
  19. Why the rows returns by “explain” is not equal to count()?
  20. Can I resolve this with pure mysql? (joining on ‘;’ separated values in a column)
  21. Using an IF Statement in a MySQL SELECT query
  22. MySQL Inner Join Query Multiple Tables
  23. Difference between these two joining table approaches?
  24. MySQL LEFT JOIN 3 tables
  25. What is the default MySQL JOIN behaviour, INNER or OUTER?
  26. MySQL Join Where Not Exists
  27. how to use a like with a join in sql?
  28. When to use a left outer join?
  29. Mysql Counting the consecutive rows that match
  30. MySQL Multiple Joins in one query?

Leave a Comment