SQL to combine two select statements from one table

Transact-SQL
1

I am looking to get a response back from my DB where I return 3 columns named: LOADNUMBER, REVENUE, FACTOR. Note: REVENUE and FACTOR columns are summed totals.

I have tried the following but only getting both the sums being placed into the REVENUE column.

SELECT * FROM (
(SELECT e.LOADNUMBER, sum(Amount) AS REVENUE
FROM tbDESCRIPT AS d
INNER JOIN tbEXPENSES as e ON d.ID=e.DESCRIPT_ID
WHERE LOADNUMBER BETWEEN '2020001' AND '2020099'
AND e.SECT_ID = '1'
GROUP BY LOADNUMBER)

UNION

(SELECT e.LOADNUMBER, sum(Amount) AS FACTOR
FROM tbDESCRIPT AS d
INNER JOIN tbEXPENSES as e ON d.ID=e.DESCRIPT_ID
WHERE LOADNUMBER BETWEEN '2020001' AND '2020099'
AND e.SECT_ID = '6'
AND e.DESCRIPT_ID ='88'
GROUP BY LOADNUMBER))
COLLECTION;

My result comes back as:
LOADNUMBER REVENUE
2020003 36.00
2020003 1226.22
2020004 33.00
2020004 1100.00
2020005 81.00
2020005 2770.48
2020006 30.00
2020006 1025.50
2020007 75.00
2020007 2562.86
2020008 96.00
2020008 3456.86
2020009 30.00
2020009 1034.73
2020010 23.20
2020010 797.91

What I'm attempting to do is get a return like:
LOADNUMBER REVENUE FACTOR
2020003 1226.22 36.00
2020004 1100.00 33.00
2020005 2770.48 81.00

2

Just do one query

AND ((e.SECT_ID = '6'
AND e.DESCRIPT_ID ='88 ) OR (e.SECT_ID = '1'))
3 
SELECT e.LOADNUMBER, 
    sum(CASE WHEN e.SECT_ID = '1' THEN Amount ELSE 0 END) AS REVENUE,
    sum(CASE WHEN e.SECT_ID <> '1' THEN Amount ELSE 0 END) AS FACTOR
FROM tbDESCRIPT AS d
INNER JOIN tbEXPENSES as e ON d.ID=e.DESCRIPT_ID
WHERE LOADNUMBER BETWEEN '2020001' AND '2020099'
AND ((e.SECT_ID = '1')
OR (e.SECT_ID = '6'
AND e.DESCRIPT_ID ='88'))
GROUP BY LOADNUMBER
1 Like
4

Well it seems I was over thinking this way too much. Thanks Yosiasz and Scott. Working as I needed. Much appreciated!

Now to add on another snag:

I want to pull a column from my BILLING_TABLE and add to the report.

I am trying the following:

SELECT f.BROKER_AGENCY, e.LOADNUMBER,
sum(CASE WHEN e.SECT_ID = '1' THEN Amount ELSE 0 END) AS REVENUE,
sum(CASE WHEN e.SECT_ID <> '1' THEN Amount ELSE 0 END) AS FACTOR
FROM tbDESCRIPT AS d
INNER JOIN tbEXPENSES as e ON d.ID=e.DESCRIPT_ID
INNER JOIN BILLING_TABLE f ON e.LOADNUMBER = f.PETS_LOAD_NUMBER
WHERE LOADNUMBER BETWEEN '2020000' AND '2020099'
AND ((e.SECT_ID = '1')
OR (e.SECT_ID = '6'
AND e.DESCRIPT_ID ='88'))
GROUP BY LOADNUMBER

Receiving the following error: Column 'BILLING_TABLE.BROKER_AGENCY' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Not sure how to resolve this. ----- disregard... just had to add BROKER_AGENCY into the group by as well.

AKA:
SELECT e.LOADNUMBER,
sum(CASE WHEN e.SECT_ID = '1' THEN Amount ELSE 0 END) AS REVENUE,
sum(CASE WHEN e.SECT_ID <> '1' THEN Amount ELSE 0 END) AS FACTOR,
f.BROKER_AGENCY
FROM tbDESCRIPT AS d
INNER JOIN tbEXPENSES as e ON d.ID=e.DESCRIPT_ID
INNER JOIN BILLING_TABLE f ON e.LOADNUMBER = f.PETS_LOAD_NUMBER
WHERE LOADNUMBER BETWEEN '2020000' AND '2020099'
AND ((e.SECT_ID = '1')
OR (e.SECT_ID = '6'
AND e.DESCRIPT_ID ='88'))
GROUP BY LOADNUMBER, BROKER_AGENCY