ORACLE SQL: How to group and count data by column?

Question

I have two tables:

Table A:

ID	CODE	time-stamp
Aid_z	code_z	2020-07-21
...	...	...
Aid_1	code_1	2020-11-30
Aid_2	code_2	2020-11-30
Aid_3	code_2	2020-11-30
Aid_4	code_1	2020-11-30
Aid_5	code_3	2020-11-30
...	...	...
Aid_n	code_x	2021-04-06

ID is unique, there is 67 distinct CODEs.

Table B:

ID	ID_A	status	value	time-stamp
Bid_z	Aid_z	z	...	2020-07-21
...	...	...	...	...
Bid_1	Aid_1	1	101.1	2020-11-30
Bid_2	Aid_2	0	87.6	2020-11-30
Bid_3	Aid_3	0	76.2	2020-11-30
Bid_4	Aid_4	1	106.2	2020-11-30
Bid_5	Aid_5	2	124.6	2020-11-30
...	...	...	...	...
Bid_n	Aid_n	x	...	2021-04-06

ID is unique, ID_A is the same as ID from Table A.

What I need to do is:

Count how much of all records are each day
Count amount of each distinct CODEs
Present them as a columns and each row should be representing one day
Only for status "0" or "1" from Table B
Only for records between 2020-11-30 and 2021-02-06

In the end reasult should looks like:

date (per day)	sum_of_this_day	code_1	code_2	code_2	...	code_z
2020-11-30	35	5	0	10	...	x
2020-11-31	60	6	5	12	...	x
...	...	...	...	...	...	...
2021-02-06	47	6	5	12	...	x

I alredy count with WHERE-clause taking into consideration date range and status. But I have problem to put everything in one query that give me the reasult as stated in my post - each day separately nad each column representing different CODE. Is it even possible to make one like that? — Jacek Krzyżanowski
– Jacek Krzyżanowski, Commented Apr 9, 2021 at 14:35

Aman Singh Rajpoot · Accepted Answer · 2021-04-11 04:29:37Z

1

I tried this solution, let me know if this helps. I filtered rows on your conditions Only for status "0" or "1" from Table B Only for records between 2020-11-30 and 2021-02-06 then I have a table with those codes which satisfy your condition and I used ``pivot``` to count distinct codes for each day and in a separate table, I counted the total codes of that day and joined these two tables to get the final output distinct_day, total_codes, code1_count, code2_count,...codez_count

With filtered_table as (select A.time_stamp time_stamp , A.code code from A inner join B
                        on(A.id = B.id_a)
                        where B.status in (0, 1)
                        and A.time_stamp between to_date('2020-11-30','yyyy-mm-dd')  and to_date('2021-02-06','yyyy-mm-dd')),
     pivot_table as (select * from ( select time_stamp t1, code from filtered_table)
                    pivot ( count(code) for code  in ( 'code_1', 'code_2', 'code_3', 'code_z') )),
     every_day_cnt as ( SELECT time_stamp, count(code) cnt from filtered_table GROUP BY time_stamp)
SELECT A.*, B.cnt from pivot_table  A inner join every_day_cnt B on (A.t1 = b.time_stamp);

edited Apr 11, 2021 at 4:29

answered Apr 9, 2021 at 17:14

Aman Singh Rajpoot

1,4891 gold badge9 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

astentx Over a year ago

Those dots between code values are not a working syntax.

Aman Singh Rajpoot Over a year ago

yes, and it means you have to put your code_1, code_2 till your hypothetical code_z in order to get an aggregation of all code present in the table.

Collectives™ on Stack Overflow

ORACLE SQL: How to group and count data by column?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related