Ann Ann - 4 days ago 6
MySQL Question

combining similar data in MySQL

I have following data :

date | source | session | device
5/1/2016 | facebook.com/social | 5 | mobile
5/1/2016 | facebook.com/post | 50 | desktop
5/1/2016 | facebook.com/commun | 25 | mobile
5/1/2016 | pintrest.com/social | 15 | mobile
5/1/2016 | pintrest.com/commun | 25 | mobile


I need data like:

date | source | session | device
5/1/2016 | facebook | 30 | mobile
5/1/2016 | facebook | 50 | desktop
5/1/2016 | pintrest | 40 | mobile


I am using MySQL database

Answer

Assuming you can use the first occurrence of the dot as your shortened URL ('.'), then the following will work for you.

select
  date
  , LEFT(source, LOCATE('.', source) - 1) as 'short_source'
  , sum(sessions) as  'sessions'
  , device
from date
group by 
  date
  , LEFT(source, LOCATE('.', source) - 1)
  , device

SQL Fiddle

Comments