CODEWITHSUNDEEP
sqloracle-database
Priya Damotharan
Priya Damotharan

Reputation: 3

Query with more than one group by columns, get the row with max value of one column

Below is my query . I am extracting total data by month and institute.

select to_char(BILL_TRANSACTION_DATE, 'YYYY-MM') as Month,
institution_id as institutions,
Count(*) as total
from bill_tx tx
group by to_char(BILL_TRANSACTION_DATE, 'YYYY-MM'), institution
order by to_char(BILL_TRANSACTION_DATE, 'YYYY-MM') desc

in bill_tx table I have one column called version. I want to change the above query to fetch row with max version value .

Actually I want total number of records by bill_transaction month and institution.

Condition is by disctint BILL_REF_NBR or count only one record if more than one version exist.

I tried to add below in query

where tx.version =  (SELECT MAX(version) FROM bill_tx b WHERE b.institution_id  = tx.institution_id  and  rownum = 1  group by b.BILL_REF_NBR )

But not getting correct data

Below sample data

+----+------------+-------------+--------------+---------+
| id |   Month    | institution | bill_ref_nbr | version |
+----+------------+-------------+--------------+---------+
|  1 | 2020-08-01 | child care  | A001         |       1 |
|  2 | 2020-08-03 | child care  | A001         |       2 |
|  3 | 2020-08-04 | child care  | M001         |       1 |
|  4 | 2020-08-09 | child care  | K001         |       1 |
|  5 | 2020-08-09 | child care  | K001         |       2 |
|  6 | 2020-08-04 | infant care | AC01         |       1 |
|  7 | 2020-08-05 | infant care | AC01         |       2 |
|  8 | 2020-08-07 | Playgroup   | PL01         |       1 |
|  9 | 2020-07-07 | infant care | XX01         |       1 |
| 10 | 2020-07-07 | infant care | XX01         |       2 |
+----+------------+-------------+--------------+---------+

output should be

+---------+-------------+-------+
|  Month  | institution | total |
+---------+-------------+-------+
| 2020-08 | child care  |     3 |
| 2020-08 | infant care |     1 |
| 2020-08 | Playgroup   |     1 |
| 2020-07 | infant care |     1 |
+---------+-------------+-------+

Correct my query if it wrong.

Upvotes: 0

Views: 77

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

With your last request comment it seems you want something completely different. This is what you say in your request:

I want to change the above query to fetch row with max version value.

And I think: No, you want to ignore the version. Because in the comment you say:

For each bill_ref_nbr I have more than one record with different version. I should take count as one if more than one version exist. Basically distinct bill_ref_nbr

You want to COUNT(DISTINCT bill_ref_nbr):

select
  to_char(bill_transaction_date, 'yyyy-mm') as month,
  institution_id as institutions,
  count(distinct bill_ref_nbr) as total
from bill_tx tx
group by to_char(bill_transaction_date, 'yyyy-mm'), institution
order by to_char(bill_transaction_date, 'yyyy-mm') desc, , institution;

Upvotes: 1

Atif
Atif

Reputation: 2210

Updated based on your comment.

SQLFiddle

select to_char(BILL_TRANSACTION_DATE, 'YYYY-MM') as Month,
institution_id as institutions,
Count(distinct bill_ref_nbr) as total
from table1
group by to_char(BILL_TRANSACTION_DATE, 'YYYY-MM'), institution_id
order by to_char(BILL_TRANSACTION_DATE, 'YYYY-MM') desc, total desc;

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

You want the number of entries per month and institute, but you only want to count rows for the maximum version.

You can get the maximum version with MAX OVER. Depending on whether you are talking about the overall maximum version or the maximum version per month and institute or the maximum version per institute you want either

max(version) over ()

or

max(version) over (partition by to_char(bill_transaction_date, 'yyyy-mm'), institution_id)

or

max(version) over (partition by institution_id)

The complete query:

select month, institution, total
from
(
  select
    to_char(bill_transaction_date, 'yyyy-mm') as month,
    institution_id,
    version,
    max(version) over () as max_version,
    count(*) as total
  from bill_tx
  group by to_char(bill_transaction_date, 'yyyy-mm'), institution, version
)
where version = max_version
order by month desc, institution;

You can achieve the same with your original query by adding either

where version = (select max(version) from bill_tx b)

or

where (institution_id, version) in
(
  select institution_id, max(version)
  from bill_tx b
  group by institution_id
)

or

where (to_char(bill_transaction_date, 'yyyy-mm'), institution_id, version) in
(
  select to_char(bill_transaction_date, 'yyyy-mm'), institution_id, max(version)
  from bill_tx b
  group by to_char(bill_transaction_date, 'yyyy-mm'), institution_id
)

depending again what you want the maximum version relate to.

Upvotes: 0

Related Questions