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


Problem :

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


Solution :

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

Mysql Tutorials

Mysql Howto..

How to create this type of JSON data from PHP [closed]

How to load a large file into a table in MySQL

how to use different WHERE clause in left join

how to retrieve image from mysql database using java servlet and show it in HTML img tag? [closed]

How to join two tables having one will be limit in php mysql [closed]

How can I use PHP to delete all records in a MySQL database?

How to join three tables together in MySQL?

How to generate database diagram automatically? mysql

How do I fix django.db.utils.IntegrityError: duplicate key value violates unique constraint?

How to exit after “MySQL server has gone away” error?

how to call query from another table

How to select the 3rd row from a table in mysql?

MYsql how to log erroneous queries

How to decode a string that has been UTF-8 encoded twice to simple UTF-8?

How to design database using varying number of columns in table

PHP MultiDimension Array - How To Group Correctly

How do I select a value whose id don't exist as parent to others, in mysql?

MySQL Profiler - No entries in show profile

Show another select tag with contact numbers from selected group name

MySql how to avoid having to create temporary tables

how to get php connect to mysql?

How do I find the row number of the first occurrence of a value within a query?

How to set initial value and auto increment in MySQL?

Mysql: How can I name a subselect in a join to avoid joining identical subselects?

Dynamic JavaScript slideshow

How to retrieve the values from the mysql table which are not having default value

How can I speed up this MySQL query that finds the closest locations to a given latitude/longitude?

How to Select max Value from 2 tables in MySQL

How to login to website without a page refresh

How To Use Distinct Function For Two Column And Sum on PHP MySQL?