Select Report, Account and GroupBy in Sql
Hi, I am joining tables to return data, but I want this data to be grouped by a specific location, and also to count / sum the values associated with that location, at the moment I can only fetch data, but not sum / count the values of this location ... Here is my request
select brand_locations.locationName,campaign_stats.clicks,
campaign_stats.impressions,
campaign_stats.day,brand_locations.city,
click_actions.walkTo,click_actions.showMap,
click_actions.call,click_actions.coupon,driveTo,campaigns_details.state
from campaign_stats
inner join campaigns_details on campaign_stats.campaignId = campaigns_details.campaignId
inner join click_actions on click_actions.campaignId=campaign_stats.campaignId
inner join brand_locations on brand_locations.brandId = campaigns_details.brandId
where brand_locations.city = 'cape town'
Output signal
bellville 58
water front 12
bellville 4
bellville 1
century city 2
century city 4
My goal is to
bellville 63
water front 12
century city 6
thank
source to share
Try this query. I added a group by location name to the request, as well as the function sum used to calculate the total clicks.
select brand_locations.locationName,SUM(campaign_stats.clicks) as campaign_stats_cnt,
sum(campaign_stats.impressions) as impressions_cnt,
campaign_stats.day,brand_locations.city,
click_actions.walkTo,click_actions.showMap,
click_actions.call,click_actions.coupon,driveTo,campaigns_details.state
from campaign_stats
inner join campaigns_details on campaign_stats.campaignId = campaigns_details.campaignId
inner join click_actions on click_actions.campaignId=campaign_stats.campaignId
inner join brand_locations on brand_locations.brandId = campaigns_details.brandId
where brand_locations.city = 'cape town' group by brand_locations.locationName
source to share
The MySQL SUM () function retrieves the sum value of an expression that has been grouped by the GROUP BY clause. Have you tried this option?
select brand_locations.locationName,SUM(campaign_stats.clicks),
campaign_stats.impressions,
campaign_stats.day,brand_locations.city,
click_actions.walkTo,click_actions.showMap,
click_actions.call,click_actions.coupon,driveTo,campaigns_details.state
from campaign_stats
inner join campaigns_details on campaign_stats.campaignId = campaigns_details.campaignId
inner join click_actions on click_actions.campaignId=campaign_stats.campaignId
inner join brand_locations on brand_locations.brandId = campaigns_details.brandId
where brand_locations.city = 'cape town' group by brand_locations.locationName
source to share
You can use sum()
to get the results you want from the result you get
SELECT title, SUM(count)
FROM (resultQuery)
GROUP BY title
NOTE. Replace the header on name
your first column with the count
name of the second column and resultQuery
with the query you have above
And yes, this is a hacky subquery way of doing it ....
source to share