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
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).