Two aggregate functions in MS Access SQL?

Question

SELECT c.Category, SUM(c.[Market Value]), SUM([p.Market Value])
FROM Dec AS c, [NOV] as p
GROUP BY c.Category
;

Tells me the category is not part of the aggregate function. I just want to see the sums of each category for both tables. I do not want only certain values to appear so should I use Join?

EDIT:

SELECT c.[Category],  SUM(c.[Market Value]), SUM(p.[Market Value])
FROM Dec AS c
INNER JOIN Nov AS p ON c.ID = p.ID
GROUP BY c.category, p.Category
;

This is what I wrote using JOIN, it just runs the query for nonstop. Do I just wait? There are 80,000 rows of data, but doing one SUM grouped by category for just one month returns it instantly, why is it so hard to do two tables?

you should change you database design. Having a table per month is a bad idea. To answer your question: you need to use UNION ALL — cha
– cha, Commented Mar 23, 2016 at 5:47
I agree, but it is not my rules. Furthermore there are around 80K rows of data per month. Thanks for the answer, appreciate it. — J.H
– J.H, Commented Mar 23, 2016 at 5:49
Actually, I just tried it. I do not want the sum of both months together :( I would like the sum of each month in two separate columns. — J.H
– J.H, Commented Mar 23, 2016 at 5:57
use union all, but in the Sum column use SUM(iif(month='Dec', [Market Value], 0)) as Dec_Value, SUM(iif(month='Nov', [Market Value], 0)) as Nov_Value. Comment back if you still have troubles — cha
– cha, Commented Mar 23, 2016 at 6:06
Well, the tables do not actually have a month or date column : /. It is simply the name of the table. There is also no column that will distinclty distinguish between the tables. You have given me the solution though, I just need to find a substitute for 'month=dec'. Although I am interested, what is the meaning of the 0? — J.H
– J.H, Commented Mar 23, 2016 at 6:22

cha · Accepted Answer · 2016-03-23 06:23:01Z

1

You need to UNION ALL your data:

SELECT Category, [market Value]
FROM DEC
UNION ALL 
SELECT Category, [market Value]
FROM NOV

When you run this query you will get the combined data for two months. If you want to get the sum for the combined data the just use the query:

SELECT Category, SUM([market Value]) as sum_value
FROM (
SELECT Category, [market Value]
FROM DEC
UNION ALL 
SELECT Category, [market Value]
FROM NOV
) as A
GROUP BY Category

Now, this is not what you want. You want two columns: one for Nov, one for Dec. To do this you need to slightly modify the UNION ALL query:

SELECT Category, [market Value], 'Dec' as type
FROM DEC
UNION ALL 
SELECT Category, [market Value], 'Nov' as type
FROM NOV

And the final query will be:

SELECT Category, 
SUM(iif(type='Dec', [Market Value], 0)) as Dec_Value, 
SUM(iif(type='Nov', [Market Value], 0)) as Nov_Value
FROM (
SELECT Category, [market Value], 'Dec' as type
FROM DEC
UNION ALL 
SELECT Category, [market Value], 'Nov' as type
FROM NOV
) as A
GROUP BY Category

answered Mar 23, 2016 at 6:23

cha

10.4k1 gold badge20 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

J.H Over a year ago

Holy moly. That is disgustingly accurate. Thanks for the huge effort you obviously went through.

J.H Over a year ago

One question though, why do you put an 'AS A'?

Maciej Los Over a year ago

This is called Column/Table <a href="w3schools.com/sql/sql_alias.asp">alias</a>

Collectives™ on Stack Overflow

Two aggregate functions in MS Access SQL?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related