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

+3


source to share


3 answers


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

      

0


source


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

      

+1


source


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 ....

0


source







All Articles