mardi 14 juin 2016

SQL Query for two tables involving group and date range filter

Table 1 is:

+---------------------+-------------+------+-----+---------+-------+
| Field               | Type        | Null | Key | Default | Extra |
+---------------------+-------------+------+-----+---------+-------+
| date                | date        | YES  |     | NULL    |       |
| day                 | varchar(45) | YES  |     | NULL    |       |
| category            | varchar(45) | YES  |     | NULL    |       |
| city                | varchar(45) | YES  |     | NULL    |       |
| geo_setting         | varchar(45) | YES  |     | NULL    |       |
| match_type          | varchar(45) | YES  |     | NULL    |       |
| myGroup             | varchar(45) | YES  |     | NULL    |       |
| cost                | double      | YES  |     | NULL    |       |
| imp                 | int(11)     | YES  |     | NULL    |       |
| clicks              | int(11)     | YES  |     | NULL    |       |
| avgpos              | double      | YES  |     | NULL    |       |
| a_conversions       | double      | YES  |     | NULL    |       |
| ga_conversions      | double      | YES  |     | NULL    |       |
+---------------------+-------------+------+-----+---------+-------+

Table 2 is :

+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| date      | date        | YES  |     | NULL    |       |
| refID     | varchar(45) | YES  |     | NULL    |       |
| category  | varchar(45) | YES  |     | NULL    |       |
| myGroup   | varchar(45) | YES  |     | NULL    |       |
| city      | varchar(45) | YES  |     | NULL    |       |
| myTag     | varchar(45) | YES  |     | NULL    |       |
| myStatus  | varchar(45) | YES  |     | NULL    |       |
| mySource  | varchar(45) | YES  |     | NULL    |       |
| otf1      | varchar(45) | YES  |     | NULL    |       |
| otf2      | varchar(45) | YES  |     | NULL    |       |
| otf3      | varchar(45) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+

I am performing this query on table 1:

select category, city, sum(cost) as cost, sum(imp) as imp,
       sum(clicks) as clicks, (sum(avgpos)/sum(imp)) as avgpos, 
       sum(a_conversions) as a_conversions, sum(ga_conversions) as ga_conversions
from table1
where date between CAST('2016-06-11' AS DATE) AND CAST('2016-06-11' AS DATE)
group by category, city;

Actually table 1 contains >2,00,000 rows around 2500 rows for each day and table 2 contains < 10,000 rows

Now in the final results apart from what I am getting from my query I want an extra column which is from table 2 (basically the count of refIDs) where date is same and category is same city is same and myGroup is same

I am not comfortable with join and new to sql. Help is really appreciated :)

Aucun commentaire:

Enregistrer un commentaire