CODEWITHSUNDEEP
sqloracle-databasegroup-bysum
Iftekhar
Iftekhar

Reputation: 365

how to display the total sum of all individual dates in Oracle

I have a query that should display the total sum of sales of all individual dates,not the separate sales in each day. Below is the query I have tried and I am attaching a sample image of the output that I have gotten from this query. Your help would be appreciated.

sample

SELECT sc_cd,Mon,sum(NET_SAL) SALE
  FROM (SELECT TO_CHAR(to_date(deli_DT),'Mm') mm,
               sc_cd,
               TO_CHAR(to_date(deli_DT),'dd-Mon-yy') Mon,
               sum(sale_net) NET_SAL
          from bill_mas
         where sc_cd not in ('22')
           AND deli_dt BETWEEN '01-aug-15' and '31-aug-15'
           AND CANCL IS NULL
           AND sc_cd='01'
         GROUP BY TO_CHAR(to_date(deli_DT),'Mm'),
                  SC_cd,
                  TO_CHAR(to_date(deli_DT),'dd-Mon-yy'),
                  sale_net)
ORDER BY 3;

Upvotes: 0

Views: 1601

Answers (3)

Aditya Shrivastava
Aditya Shrivastava

Reputation: 329

If you are using oracle and want sales by day you can use analytic function like-

sum(net_sale) over(partition by mon) from your_table;

it will give you sale of each day like I have mad e a temp1 table like this-

id   mon        net_sale
1   05-08-15    123
1   05-08-15    23
1   05-08-15    1
1   12-08-15    23
1   12-08-15    455
1   12-08-15    122

and the output is like-

Mon            net_sale
05-08-15         147
05-08-15         147
05-08-15         147
12-08-15         600
12-08-15         600
12-08-15         600

Upvotes: 1

Alex Poole
Alex Poole

Reputation: 191235

You have the sale_net column in the group_by clause, so you will still see one row for each value - you'll only see any actual aggregation if you have two source rows with the same value. Remove that from the group by. It's also not clear why you're using a subquery; if you don't want MM in the output, just don't select it in the first place:

SELECT sc_cd,TO_CHAR(deli_DT,'dd-Mon-yy') Mon,sum(sale_net) NET_SAL
from bill_mas
where sc_cd not in('22')
and deli_dt BETWEEN '01-aug-15' and '31-aug-15'
and CANCL IS NULL
AND sc_cd='01'
GROUP BY SC_cd,TO_CHAR(deli_DT,'dd-Mon-yy')
order by 3

You should perhaps be selecting and grouping by trunc(deli_DT) rather than TO_CHAR(to_date(deli_DT),'dd-Mon-yy'), but if you need to format it anyway then it might not matter. But if deli_DT is a date field - as it seems to be, though it isn't entirely clear - then you should not be doing to_date() on it at all, as Boneist commented. You're really doing to_date(to_char(deli_dt)), with two implicit conversions using your NLS_DATE_FORMAT.

Using strings for your filter isn't a good idea though, and neither is using two-digit years; and you won't be seeing any rows which are from 2015-08-31 but after midnight; you should use explicit date conversions or literals, and use greater than/less than instead of between:

and deli_dt >= to_date('01-aug-2015', 'DD-mon-YYYY')
and deli_dt < to_date('01-sep-2015', 'DD-mon-YYYY'

Or:

and deli_dt >= date '2015-08-01'
and deli_dt < date '2015-09-01'

Upvotes: 3

Florin Ghita
Florin Ghita

Reputation: 17643

The issue is simple, you must eliminate sale_net column from group by clause.

Additionaly, if deli_DT is a Date datatype, you should write the query without to_date function. Also, you don't need two group by clauses:

SELECT 
  TO_CHAR(deli_DT,'Mm') mm,
  sc_cd,
  TO_CHAR(deli_DT,'dd-Mon-yy') Mon,
  sum(sale_net) NET_SAL 
from bill_mas
where sc_cd not in('22')
and deli_dt BETWEEN '01-aug-15' and '31-aug-15'
and CANCL IS NULL
AND sc_cd='01'
GROUP BY TO_CHAR(deli_DT,'Mm'), sc_cd,  TO_CHAR(deli_DT,'dd-Mon-yy')
order by 3;

Upvotes: 2

Related Questions