Sum in union query

[Tskutnik]

OK guys embarrassing question. I need the SQL to for the following , to GROUP
Account_Ref,
Account,
AsOf,

AND SUM
BOPMV,
EOPMV,
Prod_Inflow,
Prod_Outflow

I keep messing with this and am doing something dumb....

Here is the query...

SELECT
Account_Ref,
Account,
AsOf,
0 as BOPMV,
Prod_EOPMV as EOPMV,
Prod_Inflow,
Prod_Outflow
FROM Source_Prod_Enhanced
UNION ALL
SELECT
Account_Ref,
Account,
PriorAsOf as AsOf,
Prod_BOPMV as BOPMV,
0 as EOPMV,
0 as Prod_Inflow,
0 as Prod_Outflow
FROM Source_Prod_PriorVal

Thanks and sorry for being dumb.

... and there is a second question. There are 4 fields that are always set to 0 on one side, which I'm only doing to support the merging of the 3 like GROUP fields into one record for each. The SUM function is just to support the grouping. Is there a better way to do this and/or one that will produce results faster? My UNIONs are not very clean and tend to run slow.

Thanks everyone for the help.

 

[plog]

UNIONS are slow by nature, you are running more than just one query when you run them. One way to speed them up though is to properly structure your data, usually UNIONS are a hack around a poor table set up. So, why do you have 2 tables that you want to merge instead of having all this data in one table?

As to your UNION, I would divide and conquer and take it one step at a time. First build a query to get the Source_Prod_Enhanced like you want it. Name that query sub1. Then build a query to get the Source_Prod_PriorVal like you want it. Name that query sub2. That let's you verify that each set of data is working correctly and makes your UNION simply this:

SELECT * FROM sub1
UNION ALL
SELECT * FROM sub2

Don't try to do everything all at once. UNIONs are pain enough, make them simple and put the real logic in a different query.

 

[Tskutnik]

Plog - thanks for the note. I actually already have the data in queries. The two entities I called Tables are really queries, so the source data is already setup as best I can. Given the nature and source of this data there is no way to get it into the same source table.
Yes, agreed unions are slow by nature (makes me feel a bit better - ha). I have the 3 GROUP BY fields indexed so I'm not sure what else I can do.

If you have any suggestions on how to improve my "plugging zero and SUM" approach, Id sure appreciate it.

I'm still also looking for the right syntax to GROUP and SUM in the Union (with the code I've already written), so if you have suggestions on that also it would really help

Thank again for the help.

 

[plog]

I'm still also looking for the right syntax to GROUP and SUM in the Union [

Why? My method still works even if you are building the UNION on queries and not tables. Build sub1 and base it on the query and do the GROUP BY there.

 

[Tskutnik]

Plog - thanks again. Im not totally getting what to. I hope the following helps.

The data all starts in a table called Source. Two queries are run to setup and crate the data I need. Best I can tell I cannot merge these into 1 query, which leads me to the UNION concept. The two query names are (as in the SQL pasted before):
Source_Prod_Enhanced
Source_Prod_PriorVal

So to your suggestions:
***"First build a query to get the Source_Prod_Enhanced like you want it. Name that query sub1." - that is Source_Prod_Enhanced

*** "Build a query to get the Source_Prod_PriorVal like you want it. Name that query sub2" - That is Source_Prod_PriorVal.

So when you suggest your method I think I'm already there, and would still need the UNION's GROUP by and SUM clauses to mere the info back together. but this could be me being dense.

Maybe there is a better solution and my 2 sub queries can be combined into 1. Here they are:
***********************
To create SOURCE_PROD_ENHANCED
SELECT
Account,
AsOf,
[account] & [asof] AS Account_Ref,
EOPMV,
Prod_Return, [Prod_Inflow]+[Prod_Outflow] AS Prod_Flow
FROM Source;
*************************
To create SOURCE_PROD_PRIORVAL
(to create a new record for same EOPMV, but change the AsOf date to a different month. ... Uses the EOPMV for AsOF the current period and make that the BOPMV for current period +1 by changing the AsOf date on a record.)

SELECT

Account,
DateSerial(Year([asof]+15),Month([asof]+15)+1,1)-1 AS PriorAsOf (..... this changes the AsOf date),
[account] & [PriorAsOf] AS Account_Ref,
Prod_EOPMV AS Prod_BOPMV

FROM Source;

I've attached the database as an FYI

 

[plog]

If you need GROUP BY and SUM in Source_Prod_Enhanced, then why aren't you doing that?

 

[Tskutnik]

I need the results of the two queries aggregated together and then sorted and grouped. The _PRIORVAL query creates data I cannot create in the _ENANCED query and the UNION of those 2 queries needs to be sorted once put together.
At this point I'm only looking for the syntax for the GROUP and SUM for a UNION query.
Thanks

 

[plog]

In your prior posts you said you needed to SUM/GROUP just the data in the 1st select of the UNION query. Now you need to SUM/GROUP the entire dataset of the UNION once its UNIONED?

In that case, use your UNION as the data source in a new query:

SELECT Account_Ref, Account..., SUM(0 as BOPMV) AS TotBOPMV, SUM(EOPMV) as TotEOPMV....
FROM UnionQueryName
GROUP BY Account_Ref, Account...

Don't make things more complicated than necessary. Do one thing in your query, then if you need to do more make a new query based on that last one. When its done and it works like you want, then you can go back through it to try and cram all the logic together if that makes you happy.