Results 1 to 6 of 6
  1. #1
    Join Date
    May 2011
    Posts
    13

    Exclamation Unanswered: Have a query but need the sum of a sum

    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?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2011
    Posts
    13

    Does not seem work

    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by kiwimediapro View Post
    SUM(SUM(t1.sales)
    you're not allowed to do that (even if you had closed the parentheses properly
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    May 2011
    Posts
    13

    Ok so how does one do this then?

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

    Cheers

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •