SQL query conditions more summary, I would like to summary data is displayed in each of the detailed data of the last column, a great god, genuflect is begged conditions: when id, le phase and summary amt, summary data is displayed in each of the detailed data of the last column, the group by the data summary, cannot show all the detail data, thanks
ID LE TI amt
10 1 11
1, 2 November
2 10 3 22
2 10 4 22
3 10 5 33
3, 11 June 3
4 November 7 23
4 November 8 33
4 10 September 12
Output:
ID LE TI amt amount summary
1, 1 November 22
1, 2 November 22
2 10 3 22 44
2 10 4 22 44
3 10 5 33 33
11 June 3 3 3
4 November 7 23 56
4 November 8 33 56
4 10 September 12 12

CodePudding user response:

SELECT *
FROM the TABLE A
The JOIN (SELECT ID, LE SUM (AMT) AS the TOTAL FROM TABLE GROUP BY ID, LE) AS B ON Anderson, D=B.I D AND a. E=B.L E

CodePudding user response:

reference 1st floor RINK_1 response:

SELECT *
FROM the TABLE A
The JOIN (SELECT ID, LE SUM (AMT) AS the TOTAL FROM TABLE GROUP BY ID, LE) AS B ON Anderson, D=B.I D AND a. E=B.L E

Successful, thank god

CodePudding user response:

refer to the second floor weixin_40858110 response:

Quote: refer to 1st floor RINK_1 response:

SELECT *
FROM the TABLE A
The JOIN (SELECT ID, LE SUM (AMT) AS the TOTAL FROM TABLE GROUP BY ID, LE) AS B ON Anderson, D=B.I D AND a. E=B.L E

Success, thanks god

Also want to excuse me, if I want to send this new query results saved to the database table, how to add into statements nan? Always an error

CodePudding user response:

SELECT A. *, TOTAL
INTO NEW_TB
The FROM OLD_TB A
The JOIN (SELECT ID, LE, the SUM (AMT) AS the TOTAL FROM OLD_TB GROUP BY ID, LE) AS B ON Anderson, D=B.I D AND a. E=B.L E

CodePudding user response:

Behind the window function, please look at the data table with a column sum after the group, this is for you

CodePudding user response:

reference 4 floor lich2005 response:

SELECT A. *, TOTAL
INTO NEW_TB
The FROM OLD_TB A
The JOIN (SELECT ID, LE, the SUM (AMT) AS the TOTAL FROM OLD_TB GROUP BY ID, LE) AS B ON Anderson, D=B.I D AND a. E=B.L E

Hello, this query in SQL Server performs no problem, but the toad for oracle has been an error prompt: found 'B' : expecting: OF

CodePudding user response:

The

reference 5 floor qq_25073223 response:

window function, please look at the data table column sum after the group, after the absolute right for you

Thank you for your comment, benefit a lot

CodePudding user response:

The

reference 5 floor qq_25073223 response:

window function, please look at the data table column sum after the group, after the absolute right for you

The SELECT jy0. *, SUM (ORG_AMT)
Over (partition by ID_NO, LEND_FLAG TSF_FLAG, dates) as s
Um1
The from jy0
Where TSF_FLAG=10 and sum1 & gt;=50000
Or ACC_TYPE 11 and 11 and TSF_FLAG==sum1 & gt;=500000
The or ACC_TYPE 11 and 12 and TSF_FLAG==sum1 & gt;=2000000

Great god, help me check my opening the window function and where to use collocation, is always an error "sum1 identifier is invalid,

CodePudding user response:

Like this?

 select * from 
(
The SELECT jy0. *, SUM (ORG_AMT) 
Over (partition by ID_NO, LEND_FLAG TSF_FLAG, dates) as sum1 
The from jy0 
) as t 
The where (t.T SF_FLAG=10 and t.s um1 & gt; 
=50000)Or (t.A CC_TYPE=11 and t.T SF_FLAG=11 and t.s um1 & gt; 
=500000)Or (t.A CC_TYPE=12 and t.T SF_FLAG=11 and t.s um1 & gt; 
=2000000)

CodePudding user response:

Remove the as a try,

CodePudding user response:

references 9 f qq_25073223 response:

so?

 select * from 
(
The SELECT jy0. *, SUM (ORG_AMT) 
Over (partition by ID_NO, LEND_FLAG TSF_FLAG, dates) as sum1 
The from jy0 
) as t 
The where (t.T SF_FLAG=10 and t.s um1 & gt; 
=50000)Or (t.A CC_TYPE=11 and t.T SF_FLAG=11 and t.s um1 & gt; 
=500000)Or (t.A CC_TYPE=12 and t.T SF_FLAG=11 and t.s um1 & gt; 
=2000000)

Successful, thank you very much

CodePudding user response:

The

references to the tenth floor RINK_1 response:

removed as a try,

To solve, thank you

CodePudding user response:

references 9 f qq_25073223 response:

so?

 select * from 
(
The SELECT jy0. *, SUM (ORG_AMT) 
Over (partition by ID_NO, LEND_FLAG TSF_FLAG, dates) as sum1 
The from jy0 
) as t 
The where (t.T SF_FLAG=10 and t.s um1 & gt; 
=50000)Or (t.A CC_TYPE=11 and t.T SF_FLAG=11 and t.s um1 & gt; 
=500000)Or (t.A CC_TYPE=12 and t.T SF_FLAG=11 and t.s um1 & gt; 
=2000000)

But feel a window function and link query, window function queries a much slower speed