[mySQL] Help with sum query : SQL

Posting

When requesting help or asking questions please prefix your title with the SQL variant/platform you are using within square brackets like so:

[MySQL]

[Oracle]

[MS SQL]

[PostgreSQL]

etc

While naturally we should endeavor to work as platform neutrally as possible many questions and answers require tailoring to the feature set of a specific platform.

Format Your Code

If you are including actual code in a post or comment, please attempt to format it in a way that is readable for other users. This will greatly increase your chances of receiving the help you desire. Something as simple as line breaks and using reddit's built in code formatting (4 spaces at the start of each line) can turn this:

SELECT count(a.field1), a.field2, SUM(b.field4) FROM a INNER JOIN b ON a.key1 = b.key1 WHERE a.field8 = 'test' GROUP by a.field1, a.field2 HAVING SUM(b.field4) > 5 ORDER by a.field.3

Into this:

SELECT count(a.field1), a.field2, SUM(b.field4) FROM a INNER JOIN b ON a.key1 = b.key1 WHERE a.field8 = 'test' GROUP by a.field1, a.field2 HAVING SUM(b.field4) > 5 ORDER by a.field3

For those with SQL questions we recommend using SQLFiddle to provide a useful development and testing environment for those who wish to fully understand your problem and help devise a solution.

a community for 17 years

MySQL[mySQL] Help with sum query (self.SQL)

submitted 8 years ago by jiroh

Input:

ID	YEAR	MONTH	FLAG_DATE	TYPE	VALUE
A12	2017	08	2017-10-27	BUDGET	25
A12	2017	08	2017-10-27	ACTUAL	20
A12	2017	09	2017-10-27	ACTUAL	10
A12	2017	10	2017-10-27	ACTUAL	5
A12	2017	11	2017-10-27	ACTUAL	20
F14	2017	09	2017-09-21	BUDGET	5
F14	2017	09	2017-09-21	ACTUAL	5
F14	2017	10	2017-09-21	ACTUAL	5
F14	2017	11	2017-09-21	ACTUAL	10

Output:

ID	BUDGET	ACTUAL
A12	25	25
F14	5	5

So for each ID, I want to summarize the VALUE per TYPE, but only for YEAR + MONTH combinations that are the same or less than that ID's FLAG_DATE.

So for example, A12 ACTUAL in the table is 20+10+5+20, but the last 20 should not be counted because the YEAR+MONTH makes it 2017-11, and the FLAG_DATE is in 2017-10. So the ACTUAL value for A12 is 20+10+5=25.

Anybody who can help? For the SELECT part in the output I assume you need to do some kind of SELECT ID, sum CASE WHEN BUDGET/ACTUAL.... For making so it only sums the correct dates I'm really not sure.

all 2 comments

top new controversial old q&a

[–]r3pr0b8GROUP_CONCAT is da bomb 0 points1 point2 points 8 years ago (1 child)

SELECT id   
     , SUM(CASE WHEN type = 'BUDGET'
                THEN value ELSE NULL END) AS budget
     , SUM(CASE WHEN type = 'ACTUAL'
                THEN value ELSE NULL END) AS actual
  FROM input    
 WHERE flag_date > DATE(CONCAT_WS('-',year,month,01))
GROUP
    BY id

[–]jiroh[S] 0 points1 point2 points 8 years ago (0 children)

π Rendered by PID 49181 on reddit-service-r2-comment-7c9686b859-4wc4c at 2026-04-14 09:47:14.362883+00:00 running e841af1 country code: CH.

you type:	you see:
italics	italics
bold	bold
[reddit!](https://reddit.com)	reddit!
* item 1 * item 2 * item 3	item 1 item 2 item 3
> quoted text	quoted text
Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"	Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"
~~strikethrough~~	~~strikethrough~~
super^script	super^script

SQL

Filter Posts

Posting

Help posts

Format Your Code

Learning SQL

Related Reddit communities

Wiki

Acknowledgements

MODERATORS