Sql conditional query on table

Question

I have 2 tables.

I am trying to get all columns from articles - and the number of status where status = 0 as status0 - and the number of status where status = 1 as status1

"Get all from articles and for each article row get the number of comments where status = 0 as status0 and get the number of comments where status = 1 as status1". Possible?

Tables:

articles
========
id   name
---------
1    abc
2    def
3    ghi


comments
========
id   article_id    status
-------------------------
1    2             1
2    2             0
3    1             0
4    3             1

Expected result of articles combined with status numbers:

id   name    status0   status1
------------------------------
1    abc     1         0
2    def     1         1
3    ghi     0         1

I am using Eloquent of Laravel, but would be sufficient to see the raw sql statement. I have no clue how to query and count these status.

Thanks to fiddle I have managed to create this query, but I get an error: Note that (articles = db_surveys and comments = db_answers)

"SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.`status=1) status1` from `db_surveys` left join `db_answers` on `db_surveys`.`i' at line 1 (SQL: select `db_surveys`.`*, SUM(db_answers`.`status=0) status0, SUM(db_answers`.`status=1) status1` from `db_surveys` left join `db_answers` on `db_surveys`.`id` = `db_answers`.`surveyid` where `db_surveys`.`userid` = 6oGxr)"

Full query:

"select `db_surveys`.`*, SUM(db_answers`.`status=0) status0, SUM(db_answers`.`status=1) status1` from `db_surveys` left join `db_answers` on `db_surveys`.`id` = `db_answers`.`surveyid` where `db_surveys`.`userid` = 123 group by `db_surveys`.`id`"

**

Final query:

**

SELECT 
  `s`.*,
   SUM(`a`.`status`='pending') `status0`, 
   SUM(`a`.`status`='confirmed') `status1` 
FROM
  `db_surveys` s
  LEFT JOIN `db_answers` a
    ON `s`.`id` = `a`.`surveyid` 
WHERE `s`.`userid` = '6oGxr' 
GROUP BY `s`.`id`

SELECT s.*,count(s.status=0),count(s.status=1) FROM articles s LEFT JOIN comments a ON(s.id = a.article_id) GROUP BY s.id — Shlomo
– Shlomo, Commented Apr 29, 2014 at 12:39

M Khalid Junaid · Accepted Answer · 2014-04-29 13:05:08Z

2

You can use sum() with expression to get the count on basis your conditions,using expression in sum will result as boolean o or 1

SELECT a.*
,SUM(`status` =0) status0   
,SUM(`status` =1) status1   
FROM articles a
LEFT JOIN comments c ON(a.id = c.article_id)
GROUP BY a.id

Fiddle Demo

Edit In your original query you are not using back-ticks properly

SELECT 
  `s`.*,
   SUM(`a`.`status`=0) `status0`, 
   SUM(`a`.`status`=1) `status1` 
FROM
  `db_surveys` s
  LEFT JOIN `db_answers` a
    ON `s`.`id` = `a`.`surveyid` 
WHERE `s`.`userid` = 123 
GROUP BY `s`.`id`

edited Apr 29, 2014 at 13:05

answered Apr 29, 2014 at 12:35

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

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

4 Comments

Shlomo Over a year ago

Thanks the fiddle works! In my query I also have a where. Although I setup the query like you, I get an error. Please see my edit. I am using db_answers.status because db_surveys also has a column status

M Khalid Junaid Over a year ago

@Indianer see my updated answer you are not using proper back-ticks

Shlomo Over a year ago

DO you happen to know how to set an default value 0 to the sum of the status? Because I get a default "undefined" if there are no status found.

M Khalid Junaid Over a year ago

I guess you are asking for the null values returned from sum for this you can use somthing like COALESCE(SUM(a.status='pending')) status0

Strawberry · Accepted Answer · 2014-04-29 12:42:54Z

0

Possibly something like this:

SELECT a.*, COUNT(c.*) AS status0, COUNT(c2.*) AS status1
FROM articles AS a
LEFT JOIN comments AS c ON c.article_id = a.id AND c.status = 0
LEFT JOIN comments AS c2 ON c.article_id = a.id AND c.status = 1

edited Apr 29, 2014 at 12:42

Strawberry

34k14 gold badges43 silver badges57 bronze badges

answered Apr 29, 2014 at 12:41

user3455370

211 bronze badge

Comments

John · Accepted Answer · 2014-04-29 12:42:40Z

-1

Not the pretiest one, but it work :

SELECT articles.id, articles.name, 
     (SELECT COUNT(*) FROM comments WHERE article_id = articles.id AND status = 0), 
     (SELECT COUNT(*) FROM comments WHERE article_id = articles.id AND status = 1) 
FROM articles;

http://sqlfiddle.com/#!2/123b68/4

answered Apr 29, 2014 at 12:42

John

3482 silver badges7 bronze badges

Collectives™ on Stack Overflow

Sql conditional query on table

Final query:

3 Answers 3

Fiddle Demo

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Final query:

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related