can we do remove union all and do in one query

Hi,
i have union all query

select
a.id
sum(a.QTY)
from
table fct a
left outer join dim b
on a.to_control_id =b.control_id
order by
a.id

union all

select
a.id
sum(a.QTY)
from
table fct a
left outer join dim b
on a.from_control_id =b.control_id
order by
a.id

i get results like below

id qty
1   0
2   5
3   7

1   9
2   10
3   11


i want result like below

id qty
1   9
2   15
3   18

how can i do it is it possible to remove union all and do in one query?
sam2929Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dsackerContract ERP Admin/ConsultantCommented:
At the least you'll need to union your fct table, then join the dim table once.:
SELECT	a.id,
        SUM(a.QTY) AS Qty
FROM   (SELECT id, to_control_id   AS control_id FROM fct UNION ALL
        SELECT id, from_control_id AS control_id FROM fct ) a
JOIN	dim b ON b.control_id = a.control_id
GROUP BY a.id

Open in new window

0
sam2929Author Commented:
my question was can we take out union all and do work around
0
Mark ElySenior Coldfusion DeveloperCommented:
Yes
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

dsackerContract ERP Admin/ConsultantCommented:
>> my question was can we take out union all and do work around

This would work, too:
SELECT  a.id,
        SUM(a.QTY) AS Qty
FROM    fct a
WHERE   a.to_control_id   IN (SELECT control_id FROM dim)
OR      a.from_control_id IN (SELECT control_id FROM dim)
GROUP BY a.id

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sam2929Author Commented:
can you please post the query
0
dsackerContract ERP Admin/ConsultantCommented:
Just posted an alternate query above yours.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Your query can't work because doesn't have a GROUP BY clause.
Anyway, this may be what you're looking for:
select a.id, sum(a.QTY)
from fct a
group by a.id
order by a.id

Open in new window

0
awking00Information Technology SpecialistCommented:
>>i get results like below
 id qty
 1   0
 2   5
 3   7

 1   9
 2   10
 3   11<<
Can you post some sample data (i.e.id, from_control_id, to_control_id, qty) that would produce those results?
0
sam2929Author Commented:
when i do join using OR i double up the numbers

on a."FROM_QUOTA_ACCOUNT_SID"=f."QUOTA_ACCOUNT_SID"
or a."TO_QUOTA_ACCOUNT_SID"=f."QUOTA_ACCOUNT_SID"
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
My solution don't have a JOIN. Did you try it?
0
awking00Information Technology SpecialistCommented:
It would be most helpful if we had some sample data to test with. How many rows did your join query produce that doubled the numbers? Perhaps you could supply a portion of that data (you could obfuscate the data if it's proprietary) which would demonstrate that condition.
0
dsackerContract ERP Admin/ConsultantCommented:
Sam, because you have LEFT JOINs on your dim table, that really means you want all the values from the fac table regardless of whether they exist or not in the dim table. Therefore, if that is your intention, Vitor's suggested query (without the JOIN) would work.

However, if you want to restrict your query to only those records where the to_control_id and the from_control_id are found in the dim table, then you may want to use an INNER JOIN instead of a LEFT JOIN.

I second the recommendation that you supply some sample data, and that using that sample data only, you provide what you expect to see from it.
0
David ToddSenior Database AdministratorCommented:
Hi,

I have to suggest that you have managed to misapply dsackers suggestion, as I can't see how it could double the numbers, and the snippet you posted doesn't feel right.

Regards
  David
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.