I want to select data day by day. Condition: data will be cut the day from 10:00 pm to tomorrow 10 pm. For example:
| REGTIME | T8 | T9 | T10 | | 2008-12-03 | 879 | 785| 542 | | 2008-12-04 | 556 | 454| 321 | | 2008-12-05 | 678 | 122| 212 | | 2008-12-06 | 887 | 544| 214 |
(2008-12-03 data will be show from 2008-12-02 10:00pm to 2008-12-03 10:00 pm)…
That query shows me all date, but it shows day with all same value.
SELECT REGTIME2, A.T8,T9,T10 FROM ( SELECT SUM(CASE WHEN sdudent_type = 'AAAA1' THEN 1 ELSE 0 END) AS T8, SUM(CASE WHEN sdudent_type = 'AAAA2' THEN 1 ELSE 0 END) AS T9, SUM(CASE WHEN sdudent_type = 'AAAA3' THEN 1 ELSE 0 END) AS T10 FROM class_men C WHERE REGTIME >= TO_DATE(:REGTIME_from,'YYYYMMDDHH24MISS') AND REGTIME < TO_DATE(:REGTIME_to,'YYYYMMDDHH24MISS') ) A, ( select distinct to_char(REGTIME,'YYYY-MM-DD') AS REGTIME2 from class_men group by REGTIME order by to_char(REGTIME,'YYYY-MM-DD') desc ) B
Advertisement
Answer
You are getting the results duplicated for all days as you are using a CROSS JOIN
(written in the legacy syntax using a comma) to join the two SELECT
clauses and there is no correlation between those two statements.
You appear to want something like:
SELECT TRUNC( RegTime ) AS RegTime, COUNT(CASE WHEN sdudent_type = 'AAAA1' THEN 1 END) AS T8, COUNT(CASE WHEN sdudent_type = 'AAAA2' THEN 1 END) AS T9, COUNT(CASE WHEN sdudent_type = 'AAAA3' THEN 1 END) AS T10 FROM class_men C WHERE REGTIME >= TO_DATE(:REGTIME_from,'YYYYMMDDHH24MISS') AND REGTIME < TO_DATE(:REGTIME_to,'YYYYMMDDHH24MISS') GROUP BY TRUNC( RegTime );
or using TO_CHAR( RegTime, 'YYYY-MM-DD' )
instead of TRUNC( RegTime )
.
Which, for the sample data:
CREATE TABLE class_men ( regTime, sdudent_type ) AS SELECT DATE '2021-01-01' + INTERVAL '1' MINUTE * LEVEL, 'AAAA1' FROM DUAL CONNECT BY LEVEL <= 250 UNION ALL SELECT DATE '2021-01-01' + INTERVAL '1' MINUTE * LEVEL, 'AAAA2' FROM DUAL CONNECT BY LEVEL <= 42 UNION ALL SELECT DATE '2021-01-01' + INTERVAL '1' MINUTE * LEVEL, 'AAAA3' FROM DUAL CONNECT BY LEVEL <= 13 UNION ALL SELECT DATE '2021-01-02' + INTERVAL '1' MINUTE * LEVEL, 'AAAA1' FROM DUAL CONNECT BY LEVEL <= 99 UNION ALL SELECT DATE '2021-01-02' + INTERVAL '1' MINUTE * LEVEL, 'AAAA2' FROM DUAL CONNECT BY LEVEL <= 17 UNION ALL SELECT DATE '2021-01-02' + INTERVAL '1' MINUTE * LEVEL, 'AAAA3' FROM DUAL CONNECT BY LEVEL <= 24 UNION ALL SELECT DATE '2021-01-03' + INTERVAL '1' MINUTE * LEVEL, 'AAAA1' FROM DUAL CONNECT BY LEVEL <= 23 UNION ALL SELECT DATE '2021-01-03' + INTERVAL '1' MINUTE * LEVEL, 'AAAA3' FROM DUAL CONNECT BY LEVEL <= 50;
Would output (for the range 20210101000000
to 20210104000000
):
REGTIME | T8 | T9 | T10 :------------------ | --: | -: | --: 2021-01-01 00:00:00 | 250 | 42 | 13 2021-01-03 00:00:00 | 23 | 0 | 50 2021-01-02 00:00:00 | 99 | 17 | 24
(Note: the date format depends on the NLS_DATE_FORMAT
session parameter [unless you use TO_CHAR
].)
db<>fiddle here