CODEWITHSUNDEEP
sqloracle-databasepostgresql
Asim
Asim

Reputation: 976

Group days to month(Summed) in single row

I'm having some difficulties to display a report that can group all dates to month but also sum "Spend" per day in month.

My SQL query creates this: (retrieves all days)

  Date                            Name                Spend
    2018-01-03 00:00:00.000000  xxxxxxxxx   34.699999999999996
    2018-01-03 00:00:00.000000  xxxxxxxxx   31.89999999999999
    2018-01-03 00:00:00.000000  xxxxxxxxx   29.49
    2018-01-03 00:00:00.000000  xxxxxxxxx   52.15
    2018-01-03 00:00:00.000000  xxxxxxxxx   101.07000000000001
    2018-01-03 00:00:00.000000  xxxxxxxxx   104.37999999999998

What I want is: (retrieve for month instead of days)

    Month        Name                Spend
    2018-01  xxxxxxxxx   34.69999999999999(summed monthly)
    2018-02  xxxxxxxxx   31.89999999999999(summed monthly)
    2018-03  xxxxxxxxx   29.49(summed monthly)
    2018-04  xxxxxxxxx   52.15(summed monthly)
    2018-05  xxxxxxxxx   101.07000000000001(summed monthly)
    2018-06  xxxxxxxxx   104.37999999999998(summed monthly)

This is my SQL-query:

SELECT
  "GregorianDate" as date_year,
  "AccountName" as account_name,
  sum("Spend") as spend
FROM bingads_redperformance.keyword_performance_report
WHERE
  EXTRACT(YEAR FROM ("GregorianDate")) = 2018
GROUP BY date_year, account_name
LIMIT 1000

I tried adding MONTH(date_year) but it did not work. I hope someone can guide me in right direction.

Upvotes: 1

Views: 103

Answers (2)

Kasun Gamage
Kasun Gamage

Reputation: 346

I tested this with sample data. working fine.

SELECT
  TO_CHAR("GregorianDate", 'YYYY-MM') as month,
  "AccountName" as account_name,
  SUM("Spend") as spend
FROM bingads_redperformance.keyword_performance_report
WHERE
  EXTRACT(YEAR FROM ("GregorianDate")) = 2018
GROUP BY TO_CHAR("GregorianDate", 'YYYY-MM'),"AccountName" ;

Upvotes: 1

PKey
PKey

Reputation: 3841

How about this (you should group by month ):

SELECT
  EXTRACT(MONTH FROM ("GregorianDate")) as date_month,
  "AccountName" as account_name,
  sum("Spend") as spend
FROM bingads_redperformance.keyword_performance_report
WHERE
  EXTRACT(YEAR FROM ("GregorianDate")) = 2018
GROUP BY  date_month, account_name
LIMIT 1000

Upvotes: 1

Related Questions