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