CODEWITHSUNDEEP
sqlsql-serverunionmicrosoft-query
Lightsout
Lightsout

Reputation: 3767

Alternative to UNION similar queries

I have a query similar to the following where I need to UNION a result from the numbers 5 to 15. Instead of writing 10 of these queries is there a better way to do this? I need to plug this into EXCEL as Microsoft query for a spreadsheet.

select SUM(rd.rd_net_cost)
from hqpm..receiver_d rd
inner join hqpm..re r on r.rcv_id = rd.rcv_id
inner join hqpm..store_ta st on st.store_id = r.st_id
where RES_NUM = 5
UNION
select SUM(rd.rd_net_cost)
from hqpm..receiver_d rd
inner join hqpm..re r on r.rcv_id = rd.rcv_id
inner join hqpm..store_ta st on st.store_id = r.st_id
where RES_NUM = 6
UNION
select SUM(rd.rd_net_cost)
from hqpm..receiver_d rd
inner join hqpm..re r on r.rcv_id = rd.rcv_id
inner join hqpm..store_ta st on st.store_id = r.st_id
where RES_NUM = 7

Upvotes: -4

Views: 163

Answers (1)

seanb
seanb

Reputation: 6685

This was posted in comments - but the OP thought it would work.

Original comment was asking why a simple group by would not work? e.g.,

select RES_NUM, SUM(rd.rd_net_cost) 
  from hqpm..receiver_d rd 
  inner join hqpm..re r on r.rcv_id = rd.rcv_id 
  inner join hqpm..store_ta st on st.store_id = r.st_id 
WHERE RES_NUM BETWEEN 5 AND 15 
GROUP BY RES_NUM

@Bakalolo's further comment is to say that it appears to work, but that he would hide the res_num column in Excel - but there's actually no need for it to be included in the output. You can just select the SUM component e.g.,

select SUM(rd.rd_net_cost) AS Cost_Total
  from hqpm..receiver_d rd 
  inner join hqpm..re r on r.rcv_id = rd.rcv_id 
  inner join hqpm..store_ta st on st.store_id = r.st_id 
WHERE RES_NUM BETWEEN 5 AND 15 
GROUP BY RES_NUM

(Also note that I named the output field as Cost_Total).

Finally note that in the original question, it was using UNION rather than UNION ALL. UNION removes duplicates - so that if (say) RES_NUM 5 totalled to 30, and RES_NUM 6 also totalled to 30, you'd only get one row out.

I assumed this is an error in the original question's approach. If not, we'll need to add an additional 'group by' (e.g., use the original answer as a CTE/source table, and group by the result).

Upvotes: 0

Related Questions