How to Group by Each Day in Pl/Sql

How to group by each day in PL/SQL?

SELECT TO_CHAR(dateTime, 'DD') DAY, TO_CHAR(dateTime, 'MM') MONTH, errormessage
FROM log
WHERE (...)
GROUP BY TO_CHAR(dateTime, 'DD'), TO_CHAR(dateTime, 'MM'), errormessage

Column aliases are no good for GROUP BY, you need the full expression.

Oracle/SQL - Grouping items by action by day over time

This would give your output:

SELECT Widget, to_char(timestamp_,'YYYY-MM-DD'), Count(Widget)
FROM Widget
WHERE timestamp_ BETWEEN to_date('YYYY-MM-DD HH24:MI:SS','%date1%') AND to_date('YYYY-MM-DD HH24:MI:SS','%date2%')
AND action LIKE 'reject'
GROUP BY Widget, to_char(timestamp_,'YYYY-MM-DD')
HAVING Count(Widget) > 1;

Of course, you'll want to replace the date variables.

group by day in Oracle doesn't appear to group by date

What I think is happening is that there is a hidden time part in that datesent field, and its grouping the entries by the exact time they were sent, rather than just looking at the date.

That's very probably what's happening. So try that:

select TRUNC(datesent), count(*) the_count from receivedmessaged where status=5000
and datesent>(to_date('20130101', 'YYYYMMDD')) group by TRUNC(datesent)

TRUNC will remove the "time part" and allow you to group by day.

---

Please note that the use of TRUNC wil invalidate your index. Take a look at your execution plan. And if needed, you should add a function-based index on TRUNC(datesend).

Oracle group by date, month and year

If datefield is a string, then just use substr(). For year and month:

Select count(1), country_of_sale, substr(datefield, 1, 6) AS yyyymm
from table 
where country_of_sale IN ('USA', 'EUROPE', 'ASIA')
group by country_of_sale, substr(datefield, 1, 6)
order by substr(datefield, 1, 6);

If datefield is a date, then use to_char():

Select count(1), country_of_sale, to_char(datefield, 'YYYY-MM') AS yyyymm
from table 
where country_of_sale IN ('USA', 'EUROPE', 'ASIA')
group by country_of_sale, to_char(datefield, 'YYYY-MM')
order by to_char(datefield, 'YYYY-MM');

How to group by date and sort in Oracle?

The only missing thing in your query is that we cannot use an alias in the group by clause, but alias can be used in order by clause so below query will work.

SELECT Max(scaleA.val) AS scaleA_val,
       Max(scaleB.val) AS scaleB_val,
       To_char(From_dt1970(scaleA.time1970), 'DD') AS day
  FROM rsdu2elarh.el008_6305119 scaleA
  FULL OUTER JOIN rsdu2elarh.el008_6305126 scaleB
    ON scaleA.time1970 = scaleB.time1970
 WHERE scaleA.time1970 > To_dt1970(To_date('2019/11/01', 'yyyy/mm/dd'))
   AND scaleA.time1970 < To_dt1970(To_date('2019/12/01', 'yyyy/mm/dd'))
 GROUP BY To_char(From_dt1970(scaleA.time1970), 'DD')
 ORDER BY day;

If You want to use an alias then you need to use a subquery

SELECT Max(scaleA_val)  AS scaleA_val,
       Max(scaleB_val) AS scaleB_val,
       day from
       (select scaleA.val  scaleA_val,
        scaleB.val AS scaleB_val,
        To_char(From_dt1970(scaleA.time1970), 'DD') AS day
  FROM rsdu2elarh.el008_6305119 scaleA
  FULL OUTER JOIN rsdu2elarh.el008_6305126 scaleB
    ON scaleA.time1970 = scaleB.time1970
 WHERE scaleA.time1970 > To_dt1970(To_date('2019/11/01', 'yyyy/mm/dd'))
   AND scaleA.time1970 < To_dt1970(To_date('2019/12/01', 'yyyy/mm/dd'))
   )
 GROUP BY day
 ORDER BY day;

Oracle SQL - Sum and group data by week

Try this

SELECT to_char(DATE - 7/24,'IYYY'), to_char(DATE - 7/24,'IW'),SUM(AMOUNT)
FROM YourTable
GROUP BY to_char(DATE - 7/24,'IYYY'), to_char(DATE - 7/24,'IW')

FIDDLE DEMO

---

Output would be:

+-----+-------+--------+
|YEAR | WEEK  | AMOUNT |
+-----+-------+--------+
|2013 | 11    | 18     |
|2013 | 13    | 3      |
+-----+-------+--------+

SQL group by date (hour)

Select TO_CHAR(date,'HH24')
from table
where date = TO_DATE('20110224', 'YYYYMMDD')
group by TO_CHAR(date,'HH24')

How to select latest value for each day in Oracle?

You can use ROW_NUMBER() analytic function such as

SELECT datetime, amount
  FROM 
  ( SELECT datetime,
           amount,
           ROW_NUMBER() OVER(PARTITION BY TRUNC(datetime) ORDER BY datetime DESC) AS rn
      FROM order_items)
 WHERE rn = 1

if there might occur ties for datetime values, then prefer using DENSE_RANK() function instead as

SELECT datetime, amount
  FROM 
  ( SELECT datetime,
           amount,
           DENSE_RANK() OVER(PARTITION BY TRUNC(datetime) ORDER BY datetime DESC) AS dr
      FROM order_items)
 WHERE dr = 1

---

Related Topics