clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1305674 fiddles created (20251 in the last week).

create table invoice ( invoice_id int, group_id int, qty numeric(10,6), price numeric( 10,6 ) ); insert into invoice ( invoice_id, group_id, qty, price ) values ( 1, 1, 525, 6.051280 ), ( 1, 1, 1, 60.512800 ), ( 1, 1, 32, 302.564000 ), ( 1, 1, 16, 75.641000 ), ( 1, 2, 400, 6.051280 ), ( 1, 2, 1, 60.512800 ), ( 1, 2, 24, 302.564000 ), ( 1, 2, 26, 75.641000 ), ( 1, 3, 50, 6.051280 ), ( 1, 3, 1, 60.512800 ), ( 1, 3, 4, 302.564000 ), ( 1, 3, 2, 75.641000 ), ( 1, 4, 50, 6.051280 ), ( 1, 4, 1, 60.512800 ), ( 1, 4, 8, 302.564000 ), ( 1, 4, 2, 75.641000 );
16 rows affected
 hidden batch(es)


select * from invoice
invoice_id group_id qty price
1 1 525.000000 6.051280
1 1 1.000000 60.512800
1 1 32.000000 302.564000
1 1 16.000000 75.641000
1 2 400.000000 6.051280
1 2 1.000000 60.512800
1 2 24.000000 302.564000
1 2 26.000000 75.641000
1 3 50.000000 6.051280
1 3 1.000000 60.512800
1 3 4.000000 302.564000
1 3 2.000000 75.641000
1 4 50.000000 6.051280
1 4 1.000000 60.512800
1 4 8.000000 302.564000
1 4 2.000000 75.641000
 hidden batch(es)


with sub_total as ( select *, sum( qty*price ) OVER( PARTITION BY invoice_id, group_id ) AS order_cost from invoice ) -- Here how I get is expected result: select *, (SELECT sum(x) from (SELECT sum( DISTINCT order_cost ) AS x FROM sub_total sub_i GROUP BY invoice_id, group_id) t) as total_cost -- Here is desired functionality: is this possible? -- sum( DISTINCT order_cost ) OVER ( PARTITION BY invoice_id ORDER BY invoice_id, group_id RANGE unbound preceeding and unbound following ) from sub_total;
invoice_id group_id qty price order_cost total_cost
1 1 525.000000 6.051280 14129.738800000000 30498.451200000000
1 1 1.000000 60.512800 14129.738800000000 30498.451200000000
1 1 32.000000 302.564000 14129.738800000000 30498.451200000000
1 1 16.000000 75.641000 14129.738800000000 30498.451200000000
1 2 400.000000 6.051280 11709.226800000000 30498.451200000000
1 2 1.000000 60.512800 11709.226800000000 30498.451200000000
1 2 24.000000 302.564000 11709.226800000000 30498.451200000000
1 2 26.000000 75.641000 11709.226800000000 30498.451200000000
1 3 50.000000 6.051280 1724.614800000000 30498.451200000000
1 3 1.000000 60.512800 1724.614800000000 30498.451200000000
1 3 4.000000 302.564000 1724.614800000000 30498.451200000000
1 3 2.000000 75.641000 1724.614800000000 30498.451200000000
1 4 50.000000 6.051280 2934.870800000000 30498.451200000000
1 4 1.000000 60.512800 2934.870800000000 30498.451200000000
1 4 8.000000 302.564000 2934.870800000000 30498.451200000000
1 4 2.000000 75.641000 2934.870800000000 30498.451200000000
 hidden batch(es)