Hello all,

I have this query so far:


SELECT SUM(t1.totalprice) as totalprice,
SUM(t1.sales) as sales, t1.`year` as `year`,
t1.mnth as `month`
from
(SELECT
Sum(crm_sales.totalprice) AS totalprice,
MONTHNAME(crm_sales.date) AS mnth,
YEAR(crm_sales.date) AS `year`,
Count(crm_sales.totalprice) AS sales,
MONTH(crm_sales.date) AS `ordering`
FROM `crm_sales`
WHERE `date` BETWEEN '2009-01-01' AND '2010-05-28'
GROUP BY crm_sales.date
) as t1
GROUP BY t1.`year`,t1.mnth
ORDER BY t1.`year`, t1.ordering

I need all these fields but also the sum of the sum of the field indicated:

SUM(t1.totalprice) as totalprice
SUM(t1.sales) as sales <------------ SUm of this sum
t1.`year` as `year`
t1.mnth as `month`

Any ideas?

if i understand what you're asking, try adding WITH ROLLUP to your GROUP BY clause

otherwise, you haven't explained the problem clearly enough

SELECT SUM(t1.totalprice) as totalprice,
SUM(t1.sales) as salessubtotal,SUM(SUM(t1.sales) as salessubtotal) as salestotal, t1.`year` as `year`,
t1.mnth as `month`
from
(SELECT
Sum(crm_sales.totalprice) AS totalprice,
MONTHNAME(crm_sales.date) AS mnth,
YEAR(crm_sales.date) AS `year`,
Count(crm_sales.totalprice) AS sales,
MONTH(crm_sales.date) AS `ordering`
FROM `crm_sales`
WHERE `date` BETWEEN '2009-01-01' AND '2010-05-28'
GROUP BY crm_sales.date
) as t1
GROUP BY t1.`year`,t1.mnth WITH ROLLUP
ORDER BY t1.`year`, t1.ordering

you're not allowed to do that (even if you had closed the parentheses properly

Thanks for that but any ideas the non how to achieve this.

Cheers

The obvious answer would be to do the arithmatic on the client side, in the reporting software.

SQL is intended to be a query language. It does aggregation, but only at one level (specified by the GROUP BY clause).

If you need more sophisticated processing, you really ought to use an application server like Microsoft SQL Reporting Services, Cognos, or WebFocus or a client side tool like Microsoft Access or Crystal Reports. These tools are built to handle this kind of problem cleanly and easily.

What you want can be done using just SQL syntax, but it is very ugly and difficult to maintain. That difficulty makes it a very poor business choice.

-PatP