I have 3 tables.First table is student.Second is student_detail and last one special_codes.
student table
studentname | invoiceno |tax |invoiceamount
Paul 10 500 1950
Georghe 20 1000 6850
Mary 30 1500 1900
Messy 40 2000 7050
studentdetail
invoiceno | code | product | amount
10 101 pencil 100
10 102 rubber 350
10 103 bag 1500
20 108 wheel 5000
20 109 tv 1500
20 110 ps 300
20 111 mouse 50
30 103 bag 1500
30 105 keyboard 400
40 111 mouse 50
40 112 car 7000
I can join these two table like this and get result table
select s.studentname,s.tax,s.invoiceamount,st.product,sum(st.amount) from student s, studentdetail st
where s.invoiceno = st.invoiceno
group by
s.studentname,
s.tax,
s.invoiceamount,
st.product
result table
studentname tax invoiceamount product amount
Paul 500 1950 bag 1500
Paul 500 1950 pencil 100
Paul 500 1950 rubber 350
Messy 2000 7050 car 7000
Messy 2000 7050 mouse 50
Mary 1500 1900 bag 1500
Mary 1500 1900 keyboard 400
Georghe 1000 6850 mouse 50
Georghe 1000 6850 ps 300
Georghe 1000 6850 tv 1500
Georghe 1000 6850 wheel 5000
Last table is special codes.It contains only one column which is called code
specialcodes table
code
101
102
113
104
105
110
111
What i want to do is to look up studentdetail table and to find codes that are same in specialcodes.Then to sum amount values and write sum to result table as another column.Result table should be like that
result table(final)
studentname tax invoiceamount product amount taxexclude
Paul 500 1950 bag 1500 450
Paul 500 1950 pencil 100 450
Paul 500 1950 rubber 350 450
Messy 2000 7050 car 7000 50
Messy 2000 7050 mouse 50 50
Mary 1500 1900 bag 1500 400
Mary 1500 1900 keyboard 400 400
Georghe 1000 6850 mouse 50 350
Georghe 1000 6850 ps 300 350
Georghe 1000 6850 tv 1500 350
Georghe 1000 6850 wheel 5000 350
CodePudding user response：
You can use analytic functions rather than GROUP BY and aggregating:
select s.studentname,
s.tax,
invoiceamount,
SUM(d.amount) OVER (PARTITION BY s.invoiceno) AS inv_amt_calc,
d.product,
d.amount,
SUM(CASE WHEN c.code IS NOT NULL THEN d.amount END)
OVER (PARTITION BY s.invoiceno) AS taxexclude
from student s
INNER JOIN studentdetail d
ON s.invoiceno = d.invoiceno
LEFT OUTER JOIN specialcodes c
ON (c.code = d.code)
Note: You can (and should) calculate the invoice amount from the studentdetails table rather than duplicating the data in the student table and violating Third-Normal Form.
Which, for your sample data, outputs:
STUDENTNAME TAX INVOICEAMOUNT INV_AMT_CALC PRODUCT AMOUNT TAXEXCLUDE Paul 500 1950 1950 rubber 350 450 Paul 500 1950 1950 pencil 100 450 Paul 500 1950 1950 bag 1500 450 Georghe 1000 6850 6850 tv 1500 350 Georghe 1000 6850 6850 wheel 5000 350 Georghe 1000 6850 6850 ps 300 350 Georghe 1000 6850 6850 mouse 50 350 Mary 1500 1900 1900 bag 1500 400 Mary 1500 1900 1900 keyboard 400 400 Messy 2000 7050 7050 mouse 50 50 Messy 2000 7050 7050 car 7000 50
If you really want a version using GROUP BY then:
SELECT s.studentname,
s.tax,
s.invoiceamount,
SUM(d.amount) OVER (PARTITION BY s.invoiceno) AS inv_amt_calc,
d.product,
d.amount,
t.taxexclude
FROM student s
INNER JOIN studentdetail d
ON s.invoiceno = d.invoiceno
LEFT OUTER JOIN (
SELECT invoiceno,
SUM(amount) AS taxexclude
FROM studentdetail
WHERE code IN (SELECT code FROM specialcodes)
GROUP BY
invoiceno
) t
ON s.invoiceno = t.invoiceno;
db<>fiddle here