CODEWITHSUNDEEP
mysqlsql
Mr.Happy
Mr.Happy

Reputation: 2647

How to use join table and count with select query

I am not good with complex sql query so I am posting this question and I will happy If you help me.

I have 2 tables. First is idea_box and second is idea_box_voting. Now first I want to join two table and then I want to count total 0 and 1 numbers of there id. Here is my two tables.

idea_box
--------

Idea_id property_id     idea_title  the_idea    user_id
25      0               Idea 25     Lorem       23      
24      0               Idea 24     Lorem       23      
23      0               Idea 23     Lorem       23          
22      0               Idea 22     Lorem       23
21      0               Idea 21     Lorem       23
20      0               Idea 20     Lorem       23

idea_box_voting
---------------

vote_id     idea_id ip              user_id     thumbs
37          25      192.168.1.14    23          1
44          25      192.168.1.14    23          1
47          25      192.168.1.14    23          0
40          25      192.168.1.14    23          1
41          24      192.168.1.14    23          1
38          24      192.168.1.14    23          0
45          24      192.168.1.14    23          1
39          23      192.168.1.14    23          1
46          23      192.168.1.14    23          0
42          23      192.168.1.14    23          0
43          22      192.168.1.14    23          1

And I want this output:

Idea_id property_id     idea_title  the_idea    user_id one_count zero_count
25      0               Idea 25     Lorem       23      3        1
24      0               Idea 24     Lorem       23      2        1
23      0               Idea 23     Lorem       23      1        2  
22      0               Idea 22     Lorem       23      1        0
21      0               Idea 21     Lorem       23      0        0
20      0               Idea 20     Lorem       23      0        0

Thanks.

Upvotes: 1

Views: 76

Answers (3)

Paul Maxwell
Paul Maxwell

Reputation: 35583

SELECT
      Idea_id
    , property_id
    , idea_title
    , the_idea
    , user_id
    , COUNT(CASE
            WHEN thumbs = 1 THEN thumbs END) one_count
    , COUNT(CASE
            WHEN thumbs = 0 THEN thumbs END) zero_count
FROM idea_box i
      LEFT JOIN idea_box_voting v
                  ON i.Idea_id = v.idea_id
GROUP BY
      Idea_id
    , property_id
    , idea_title
    , the_idea
    , user_id

Upvotes: 0

Shivang MIttal
Shivang MIttal

Reputation: 1001

Try this

Select Count(CASE WHEN vote.thumbs = 0 THEN 1 END) AS Zero_Count
       Count(CASE WHEN vote.thumbs = 1 THEN 1 END) AS One_Count
FROM idea_box as ib
left Join idea_box_voting as vote
On vote.idea_id = ib.idea_id
Group by vote.idea_id

Upvotes: 0

Ganesh Jadhav
Ganesh Jadhav

Reputation: 2848

SELECT ib.*,
    (
        SELECT COUNT(*)
        FROM idea_box_voting ibv
        WHERE ibv.idea_id = ib.idea_id AND ibv.thumbs = 1
    ) AS one_count,
    (
        SELECT COUNT(*)
        FROM idea_box_voting ibv
        WHERE ibv.idea_id = ib.idea_id AND ibv.thumbs = 0
    ) AS zero_count
FROM idea_box ib

Upvotes: 1

Related Questions