I already had a specific query to get a number of shops, but I couldn’t sum the cost field, I kept on getting an error on the sum function, but it worked when I did this;
select tab.shop_id, tab.shop_name, tab.payment_schedule, SUM(tab.num_clicks) as num_clicks, SUM(tab.cost) as cost from (select c.shop_id as shop_id, s.shop_name as shop_name, s.pay_type as payment_schedule, count(c.id) as num_clicks, count(c.id)*click_value as cost FROM Clicktracker c INNER JOIN Shop AS s ON c.shop_id=s.shop_id where c.domain='mobi.mysite.org' GROUP BY s.shop_id, click_value) as tab GROUP BY tab.shop_id;
I’m not really good at all at sql; Is there a better way to write this query?
descr of shop table;
shop_id int(10) shop_name varchar(20) pay_type float(11,2)
description of the clicktracker table;
id int(10) shop_id int(10) product_name varchar(384) shop_name varchar(384) price float(11,2) domain varchar(255) click_value float(11,2)
to say the least, that is the desc of the tables (took me a lot of editting)
Simpler, just remove the click_value from the inner group and SUM it. No need for the outer sum:
SELECT s.shop_id as shop_id, s.shop_name as shop_name, s.pay_type as payment_schedule, res.num_clicks, res.cost FROM Shop AS s INNER JOIN ( SELECT c.shop_id count(c.id) as num_clicks, SUM(click_value) as cost FROM Clicktracker c INNER JOIN Shop AS s ON c.shop_id=s.shop_id WHERE c.domain='mobi.mysite.org' GROUP BY s.shop_id, c.domain ) AS res ON s.shop_id=res.shop_id
just something extra, you have “shop_name” and id on the Shop table. Check if you really need the shop_name in your clicktracker table or if it is redundant.