Query for Daily Sales [using mysql]

[ksoup]

There has to be a better mousetrap for what I need :)



My current query looks like the following to pull all the class sums from the order totals table per day

SELECT LEFT(date_purchased,10),
(select SUM(zen_orders_total.value)
FROM
zen_orders_total
INNER JOIN zen_orders ON zen_orders.orders_id = zen_orders_total.orders_id
where zen_orders_total.class = "ot_subtotal" and left(date_purchased,10) = '2012-12-10'
GROUP BY LEFT(date_purchased,10)
order by date_purchased desc
limit 1) as ot_subtotal,

(select SUM(zen_orders_total.value)
FROM
zen_orders_total
INNER JOIN zen_orders ON zen_orders.orders_id = zen_orders_total.orders_id
where zen_orders_total.class = "ot_coupons" and left(date_purchased,10) = '2012-12-10'
GROUP BY LEFT(date_purchased,10)
order by date_purchased desc
limit 1) as ot_coupons,

(select SUM(zen_orders_total.value)
FROM
zen_orders_total
INNER JOIN zen_orders ON zen_orders.orders_id = zen_orders_total.orders_id
where zen_orders_total.class = "ot_shipping" and left(date_purchased,10) = '2012-12-10'
GROUP BY LEFT(date_purchased,10)
order by date_purchased desc
limit 1) as ot_shipping,

(select SUM(zen_orders_total.value)
FROM
zen_orders_total
INNER JOIN zen_orders ON zen_orders.orders_id = zen_orders_total.orders_id
where zen_orders_total.class = "ot_tax" and left(date_purchased,10) = '2012-12-10'
GROUP BY LEFT(date_purchased,10)
order by date_purchased desc
limit 1) as ot_tax,

(select SUM(zen_orders_total.value)
FROM
zen_orders_total
INNER JOIN zen_orders ON zen_orders.orders_id = zen_orders_total.orders_id
where zen_orders_total.class = "ot_gv" and left(date_purchased,10) = '2012-12-10'
GROUP BY LEFT(date_purchased,10)
order by date_purchased desc
limit 1) as ot_gv,


(select SUM(zen_orders_total.value)
FROM
zen_orders_total
INNER JOIN zen_orders ON zen_orders.orders_id = zen_orders_total.orders_id
where zen_orders_total.class = "ot_total" and left(date_purchased,10) = '2012-12-10'
GROUP BY LEFT(date_purchased,10)
order by date_purchased desc
limit 1) as ot_total



FROM
zen_orders
INNER JOIN zen_orders_total ON zen_orders_total.orders_id = zen_orders.orders_id
where left(date_purchased,10) = '2012-12-10'

order by date_purchased desc
limit 1


Returns the following:
left(date_purchased,10) ot_subtotal ot_coupons ot_shipping ot_tax ot_gv ot_total
12/14/12 xx.00 xx.00 xx.00 xx.00 xx.00 xx.00



I would love to have it return a bunch of days (maybe 7 at a time)

Not including the limit 1 and such returns a mess in the array or an error of operand should include one column or subquery has too many rows.

Please advise so I can keep my hair. :)

[DrByte]

Why not just use the Sales Report plugin?

[ksoup]

it's 2 basic, it doesn't include these items I listed

[ksoup]

LOL... Why yes, there is another plugin (had the one with graphs)... Thank you for the hint... the Sales Report plug in has exactly what I need! Thank you!

[ksoup]

Super Super Plugin!! Thank you for the heads up! Someone obviously built a great mousetrap!! :)