I am not getting my head around this, and wondered if anyone may be able to help me with this.
I have 2 Tables called RES_DATA
and INV_DATA
RES_DATA
Contains my Customer as below
CUSTOMER ID | NAME
1, Robert
2, John
3, Peter
INV_DATA
Contains their INVOICES as Below
INVOICE ID | CUSTOMER ID | AMOUNT
100, 1, £49.95
200, 1, £105.95
300, 2, £400.00
400, 3, £150.00
500, 1, £25.00
I am Trying to write a SELECT
STATEMENT Which will give me the results as Below.
CUSTOMER ID | NAME | TOTAL AMOUNT
1, Robert, £180.90
2, John, £400.00
3, Peter, £150.00
I think I need 2 INNER JOINS Somehow to Add the tables and SUM Values of the INVOICES Table GROUPED BY the Customer Table but honestly think I am missing something. Can't even get close to the Results I need.
This question is related to
sql
select
join
group-by
inner-join
If you need to retrieve more columns other than columns which are in group by then you can consider below query. Check it once whether it is performing well or not.
SELECT
a.[CUSTOMER ID],
a.[NAME],
(select SUM(b.[AMOUNT]) from INV_DATA b
where b.[CUSTOMER ID] = a.[CUSTOMER ID]
GROUP BY b.[CUSTOMER ID]) AS [TOTAL AMOUNT]
FROM RES_DATA a
Use subquery
SELECT * FROM RES_DATA inner join (SELECT [CUSTOMER ID], sum([TOTAL AMOUNT]) FROM INV_DATA group by [CUSTOMER ID]) T on RES_DATA.[CUSTOMER ID] = t.[CUSTOMER ID]
Two ways to do it...
GROUP BY
SELECT RES.[CUSTOMER ID], RES,NAME, SUM(INV.AMOUNT) AS [TOTAL AMOUNT]
FROM RES_DATA RES
JOIN INV_DATA INV ON RES.[CUSTOMER ID] INV.[CUSTOMER ID]
GROUP BY RES.[CUSTOMER ID], RES,NAME
OVER
SELECT RES.[CUSTOMER ID], RES,NAME,
SUM(INV.AMOUNT) OVER (PARTITION RES.[CUSTOMER ID]) AS [TOTAL AMOUNT]
FROM RES_DATA RES
JOIN INV_DATA INV ON RES.[CUSTOMER ID] INV.[CUSTOMER ID]
Source: Stackoverflow.com