CODEWITHSUNDEEP
sqloracle-database
Surya Matadewa
Surya Matadewa

Reputation: 1027

Oracle sql query sum data every month

Let's say i have selected data from oktober until desember like this

enter image description here

i want to get sum of buying customer in every months (okt-des) The result i want is like table below

enter image description here

i already know how to get last day of months
but i don't have idea query in SQL to get result like i need

Upvotes: 1

Views: 4325

Answers (3)

Артём Данилов
Артём Данилов

Reputation: 1

Should each month be a separate column?If not, then you can use window functions.

ORDER BY Name - will show the total amount for the month for each day.

DISTINCT - will remove duplicate lines

last_day(Date) - Will return the last day of the month

WITH data AS(
    SELECT Name,Buying,Date 
    FROM 'your_table'
    WHERE Data between 'start_date' and 'end_date')

SELECT DISTINCT Name,last_day(Date) as DATE,
(SUM(Buying) OVER (PARTITION BY TRUNC(Date,'MONTH'),Name ORDER BY Name) as MONTH_BUYING FROM data

As written above date is an Oracle keyword which should not be used as a column name

Upvotes: 0

A.S.
A.S.

Reputation: 320

Try this

 DESC TABLE_NAME
        NAME    VARCHAR2(20 BYTE)
        BUYING  NUMBER
        BUYING_DATE DATE

    select * from
    (
    select name,buying,RTRIM(to_char(buying_Date,'Month')) dd
    from
    TABLE_NAME
    )
    PIVOT
    (
    SUM(buying)
    for dd IN ('October','November','December')
    );

Upvotes: 1

user5683823
user5683823

Reputation:

One way to do this - if you just need the data for those three months - is to use conditional aggregation:

select   name,
         sum(case when dt >= date '2017-10-01' and dt < date '2017-11-01'
                  then buying end) as oktober,
         sum(case when dt >= date '2017-11-01' and dt < date '2017-12-01'
                  then buying end) as november,
         sum(case when dt >= date '2017-12-01' and dt < date '2018-01-01'
                  then buying end) as desember
from     YOUR_TABLE
where    dt >= date '2017-10-01' and dt < date '2018-01-01'
group by name
;

Note that date is an Oracle keyword which should not be used as a column name; I changed it to dt. YOUR_TABLE should be your actual table name.

Upvotes: 3

Related Questions