CODEWITHSUNDEEP
mysql
anonymous
anonymous

Reputation: 75

summation of count of the data from two tables in MySql

I want the total count of the columns from two tables.

I am trying this but it gives the total from one table but I want the summation from bot the tables

select DISTINCT(video_id), 
    (select count(*) as cnt from votes 
        where video_id = voteable_id 
        AND votes.type = "up") as order_col
from video_views  
ORDER BY `order_col`  DESC**

Video_views Table

video_id

    137
    136
    134
    137
    136
    137

Votes Table

   Voteable_id                     type

    137                             up
    137                             up
    134                             down
    135                             up
    134                             up
    134                             up

Its result should be

video_id   count
137        5
134        3
136        2
135        1

Upvotes: 1

Views: 55

Answers (1)

Fahmi
Fahmi

Reputation: 37473

You can try below -

select vedio_id,sum(cnt)
from
(
select video_id, count(*) as cnt from Video_views
group by video_id
union all
select Voteable_id, count(*) from Votes where votes.type = "up"
group by Voteable_id
)A group by vedio_id

Upvotes: 3

Related Questions