Hello, I have 2 queries. I need help to join them properly where I can add column CONSUMP from query 2 to query 1 as the last column.

I have query 1

SELECT dbo.PROPDB_UMMTR.DISTRICT, dbo.PROPDB_UMMTR.RATETYPE, dbo.PROPDB_TRNCD.DESCRIPT, dbo.PROPDB_UMMTR.XSIZE, dbo.PROPDB_UMSIZ.DESCRIPT AS SIZEDESC, COUNT (dbo.PROPDB_UMMTR.XSIZE) AS QUANTITY
FROM dbo.PROPDB_UMMTR INNER JOIN
dbo.PROPDB_TRNCD ON dbo.PROPDB_UMMTR.RATETYPE = dbo.PROPDB_TRNCD.TRNCDKY INNER JOIN
dbo.PROPDB_UMSIZ ON dbo.PROPDB_UMMTR.XSIZE = dbo.PROPDB_UMSIZ.SIZECODE INNER JOIN
dbo.PROPDB_CUST ON dbo.PROPDB_UMMTR.SVADDRKEY = dbo.PROPDB_CUST.SRVCKY INNER JOIN
dbo.PROPDB_ADDRS ON dbo.PROPDB_UMMTR.SVADDRKEY = dbo.PROPDB_ADDRS.ADKEY
WHERE (dbo.PROPDB_CUST.XSTATUS = 'AC') AND (dbo.PROPDB_CUST.CUSTNAME <> 'VACANT')
GROUP BY dbo.PROPDB_UMMTR.DISTRICT, dbo.PROPDB_UMMTR.RATETYPE, dbo.PROPDB_UMMTR.XSIZE, dbo.PROPDB_TRNCD.DESCRIPT, dbo.PROPDB_UMSIZ.DESCRIPT
ORDER BY dbo.PROPDB_UMMTR.DISTRICT, dbo.PROPDB_UMMTR.RATETYPE, dbo.PROPDB_UMMTR.XSIZE

Output

DISTRICT......RATETYPE......DESCRIPT......XSIZE... ...SIZEDESC......QUANTITY
1 I01 RES 5/8 1 1 INCH 10
2 I02 RES 1 1 1 INCH 20
3 I03 RES 3 3 3 INCH 5
4 I04 RES 2 2 2 INCH 30

I have query 2

SELECT dbo.PROPDB_UMMTR.DISTRICT, dbo.PROPDB_UMMTR.RATETYPE, dbo.PROPDB_TRNCD.DESCRIPT, dbo.PROPDB_UMMTR.XSIZE,
dbo.PROPDB_UMSIZ.DESCRIPT AS SIZEDESC, SUM(dbo.PROPDB_USAGO.CONSUMED)
AS CONSUMPTION
FROM dbo.PROPDB_UMMTR INNER JOIN
dbo.PROPDB_USAGO ON dbo.PROPDB_UMMTR.METERKY = dbo.PROPDB_USAGO.METERKY INNER JOIN
dbo.PROPDB_TRNCD ON dbo.PROPDB_UMMTR.RATETYPE = dbo.PROPDB_TRNCD.TRNCDKY INNER JOIN
dbo.PROPDB_UMSIZ ON dbo.PROPDB_UMMTR.XSIZE = dbo.PROPDB_UMSIZ.SIZECODE INNER JOIN
dbo.PROPDB_CUST ON dbo.PROPDB_UMMTR.SVADDRKEY = dbo.PROPDB_CUST.SRVCKY INNER JOIN
dbo.PROPDB_ADDRS ON dbo.PROPDB_UMMTR.SVADDRKEY = dbo.PROPDB_ADDRS.ADKEY
WHERE (dbo.PROPDB_CUST.XSTATUS = 'AC') AND (dbo.PROPDB_CUST.CUSTNAME <> 'VACANT')
GROUP BY dbo.PROPDB_UMMTR.DISTRICT, dbo.PROPDB_UMMTR.RATETYPE, dbo.PROPDB_UMMTR.XSIZE, dbo.PROPDB_TRNCD.DESCRIPT,
dbo.PROPDB_UMSIZ.DESCRIPT
ORDER BY dbo.PROPDB_UMMTR.DISTRICT, dbo.PROPDB_UMMTR.RATETYPE, dbo.PROPDB_UMMTR.XSIZE

Output

DISTRICT......RATETYPE......DESCRIPT......XSIZE... ...SIZEDESC......CONSUMP
1 I01 RES 5/8 1 1 INCH 20000
2 I02 RES 1 1 1 INCH 30000
3 I03 RES 3 3 3 INCH 15000
4 I04 RES 2 2 2 INCH 10000


Query with wrong count

SELECT dbo.PROPDB_UMMTR.DISTRICT, dbo.PROPDB_UMMTR.RATETYPE, dbo.PROPDB_TRNCD.DESCRIPT, dbo.PROPDB_UMMTR.XSIZE,
dbo.PROPDB_UMSIZ.DESCRIPT AS SIZEDESC, COUNT (dbo.PROPDB_UMMTR.XSIZE) AS QUANTITY, SUM(dbo.PROPDB_USAGO.CONSUMED)
AS CONSUMPTION
FROM dbo.PROPDB_UMMTR INNER JOIN
dbo.PROPDB_USAGO ON dbo.PROPDB_UMMTR.METERKY = dbo.PROPDB_USAGO.METERKY INNER JOIN
dbo.PROPDB_TRNCD ON dbo.PROPDB_UMMTR.RATETYPE = dbo.PROPDB_TRNCD.TRNCDKY INNER JOIN
dbo.PROPDB_UMSIZ ON dbo.PROPDB_UMMTR.XSIZE = dbo.PROPDB_UMSIZ.SIZECODE INNER JOIN
dbo.PROPDB_CUST ON dbo.PROPDB_UMMTR.SVADDRKEY = dbo.PROPDB_CUST.SRVCKY INNER JOIN
dbo.PROPDB_ADDRS ON dbo.PROPDB_UMMTR.SVADDRKEY = dbo.PROPDB_ADDRS.ADKEY
WHERE (dbo.PROPDB_CUST.XSTATUS = 'AC') AND (dbo.PROPDB_CUST.CUSTNAME <> 'VACANT')
GROUP BY dbo.PROPDB_UMMTR.DISTRICT, dbo.PROPDB_UMMTR.RATETYPE, dbo.PROPDB_UMMTR.XSIZE, dbo.PROPDB_TRNCD.DESCRIPT,
dbo.PROPDB_UMSIZ.DESCRIPT
ORDER BY dbo.PROPDB_UMMTR.DISTRICT, dbo.PROPDB_UMMTR.RATETYPE, dbo.PROPDB_UMMTR.XSIZE

output

shows results as query 2 with count being way off.

DISTRICT..RATETYPE..DESCRIPT..XSIZE..SIZEDESC..COU NT....CONSUMP
1 I01 RES 5/8 1 1 INCH 3000 20000
2 I02 RES 1 1 1 INCH 2000 30000
3 I03 RES 3 3 3 INCH 2200 15000
4 I04 RES 2 2 2 INCH 1500 10000


Any help is appreciated.
Thank you,