How to count and get result of two rows in Mysql Select Query?

Question

This is my table structure. sometime table1 data my repeat (Ex : Actually Id 1 should have only 4 rows but sometime it is 8 due to duplication) so avoid duplication I use GROUP BY command in select query

Table 1

|id| website|time|
-----------------
|01|facebook|20.0|
|01|google  |40.0|
|01|youtube |10.0|
|01|ebay    |30.0|
|02|facebook|50.0|
|02|ebay    |50.0|

Table 2

    |id|marks|
    ----------- 
    |01|   80|
    |02|   90|
    |03|   70|
    |04|  100|

I want to select (marks),(time on facebook) and (count of time on google & youtube) of specific user

Following select query gives (marks),(time on facebook) of user id '01'

How to receive count of time of both google and youtube of id'1' in same query ?

SELECT table2.id,table2.marks, table1.time
FROM table1
RIGHT JOIN table2 ON table1.id= table2.id
WHERE table1.website LIKE ('%facebook%')
AND table1.id=  '01'
GROUP BY table1.id, table1.website

Answer 1

You want to find the time on facebook and then the sum of youtube and google for a particular user you can use the mysql conditional sum to achieve it

select
sum(case when t1.website = 'facebook' then t1.time else 0 end) as `fb_time`,
(
  sum(case when t1.website='google' then t1.time else 0 end)+
  sum(case when t1.website='youtube' then t1.time else 0 end)
)
as `google_youtube`,
t2.marks
from table1 t1
join table2 t2 on t1.id = t2.id
where t1.id = '01' 

If you need to calculate the same for all the users then you can do it as

select
t1.id,
sum(case when t1.website = 'facebook' then t1.time else 0 end) as `fb_time`,
(
  sum(case when t1.website='google' then t1.time else 0 end)+
  sum(case when t1.website='youtube' then t1.time else 0 end)
)
as `google_youtube`,
t2.marks
from table1 t1
join table2 t2 on t1.id = t2.id
group by t1.id