NiceMan331
asked on
adding sub total to the query
hi
referring to this question
https://www.experts-exchange.com/questions/28687885/Select-Sum-Items-Of-A-Cursor.html
i have this query
this query deplays catigory and sales amounts
ok , if i want to add a row for a specific selection of catigory
for example , catigory in (2,3,4)
add sum(gross) , sum(net), sum(disount)
to the next of catigory number 4
then continue other records
referring to this question
https://www.experts-exchange.com/questions/28687885/Select-Sum-Items-Of-A-Cursor.html
i have this query
select CATEGORYID,cat , gross, net, discount,
(to_char(nvl(100 * (gross/sum(gross) over()),0),'fm9990.00')||'%' ) Cat_Perc,
(to_char(nvl(100 * (Discount/gross),0),'fm9990.00')||'%') Disc_Perc,
sum(gross) over() total_price from (
select CATEGORYID, cat ,
sum(PRICE) Gross,sum(DISCPRIC) Net,(sum(discount)) Discount , sum(quantity) QTY
from
sales_det
where
TO_char(dob, 'dd-mm-yyyy') >= '01-04-2015'
and TO_char(dob, 'dd-mm-yyyy') <= '30-04-2015'
group by CATEGORYID, cat )
order by gross desc ;
this query deplays catigory and sales amounts
ok , if i want to add a row for a specific selection of catigory
for example , catigory in (2,3,4)
add sum(gross) , sum(net), sum(disount)
to the next of catigory number 4
then continue other records
Last Comment
awking00
Can you provide some sample data for sales_det and what you would like to see as results?
I do not understand what you are asking that is different.
If you want a SUM on every row for various categories, just do the SUM with CASE and OVER from the previous question.
If you want something different, please post sample data and expected results so we can see what you are trying to do.
If you want a SUM on every row for various categories, just do the SUM with CASE and OVER from the previous question.
If you want something different, please post sample data and expected results so we can see what you are trying to do.
ASKER
If you want a SUM on every row for various categoriesexactly
just do the SUM with CASE and OVER from the previous question.you mean it will be added column ? ok , is it possible to do it as rows under various catigories ?
>>you mean it will be added column
Yes.
>> is it possible to do it as rows under various catigories ?
This is different than what you stated "exactly" in above when I asked "If you want a SUM on every row for various categories"
If you want a new row to contain the sub totals, it might be able to be done but not easily. Remember in a previous question of yours I mentioned UNION ALL and some smoke and mirrors?
This is what I meant.
It will need to be a separate query that returns the sub-totals. You'll need to include some sort columns in ALL the queries so when you do an "order by" the rows come back in the correct order.
Yes.
>> is it possible to do it as rows under various catigories ?
This is different than what you stated "exactly" in above when I asked "If you want a SUM on every row for various categories"
If you want a new row to contain the sub totals, it might be able to be done but not easily. Remember in a previous question of yours I mentioned UNION ALL and some smoke and mirrors?
This is what I meant.
It will need to be a separate query that returns the sub-totals. You'll need to include some sort columns in ALL the queries so when you do an "order by" the rows come back in the correct order.
ASKER
ok
can you post an example of that considering when catigoryid < 4 only
then i can adjust the others
can you post an example of that considering when catigoryid < 4 only
then i can adjust the others
sum(case when gross < 4 then gross end) over()
ASKER
This is as a column , I need an example as your suggestion to make row of sub total
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Ok I got it , let me do my calculation on Saturday , cause I'm now day off , thanx
Oracle Database
Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.
81K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
