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?
 
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
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Mark ElySenior Coldfusion DeveloperCommented:
Yes
0
 
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
 
awking00Commented:
>>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
 
awking00Commented:
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 DBACommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.