CODEWITHSUNDEEP
mysqlsqlgroup-bysum
desbest
desbest

Reputation: 4896

How do I get the SUM of a group in an sql query

My query is 

SELECT * 
FROM acodes
WHERE datenumber >=  '2016-12-09'
GROUP BY campaignid, acode
LIMIT 0 , 30

Results are

sql results

Is there a way to SUM() the maxworth column? I want to add up all the maxworth shown above in an sql query. The answer is not SELECT *, SUM(maxworth) as there are multiple maxworth for the same acode and campaignid.

Upvotes: 0

Views: 61

Answers (4)

spencer7593
spencer7593

Reputation: 108370

Reference the existing query as an inline view. Take the existing query, and wrap in parens, and then use that in place of a table name in another query. (The inline view will need to be assigned an alias.)

For example:

SELECT SUM(v.maxworth)
  FROM (
         -- existing query goes here, between the parens
         SELECT *
         FROM acodes
         WHERE datenumber >=  '2016-12-09'
         GROUP BY campaignid, acode
         LIMIT 0 , 30
       ) v

In MySQL, that inline view is referred to as a derived table. The way that query works.... MySQL first runs the query in the inline view, and materializes the resultset into a derived table. Once the derived table is populated, the outer query runs against that.

Upvotes: 1

JL82559
JL82559

Reputation: 43

Here is another attempt at answering your question. 

SELECT
    a.campaignid,
    a.acode,
    SUM(a.maxworth) as "SUMMAXWORTH"
FROM
    (SELECT 
        * 
    FROM acodes 
    WHERE datenumber >= '2016-12-09' 
    GROUP BY campaignid, acode 
    LIMIT 0 , 30
    ) a
GROUP BY a.campaignid, a.acodes

Upvotes: 0

JL82559
JL82559

Reputation: 43

This calculates the SUM() by unique campaignid, acode, and maxworth. You mention "there are multiple maxworth for the same acode and campaignid" which makes me think you might be wanting to treat "maxworth" as unique. 

    SELECT 
      campaignid,
      acode,
      maxworth,
      SUM(maxworth) AS 'MAXWORTH1'
    FROM acodes 
    WHERE datenumber >= '2016-12-09' 
    GROUP BY campaignid, acode, maxworth

Upvotes: 0

JL82559
JL82559

Reputation: 43

Not sure what you're asking here.

SELECT
    a.MAXWORTH1,
    SUM(a.MAXWORTH) AS "MAXWORTH2"
FROM (
    SELECT 
      CAMPAIGNID, 
      SUM(maxworth) AS "MAXWORTH1"
    FROM acodes 
    WHERE datenumber ='2016-12-05' 
    GROUP BY campaignid 
) a
GROUP BY a.MAXWORTH1

Upvotes: 1

Related Questions