Mysql – how to optimize grouped select from an already grouped select query

MySQLselect

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)

Best Answer

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.

Related Question