Oracle query - How to count two distinct rows as one based on their value-CodePudding

I have table like this:

id	physician	order_id	exam	price
0	Physycian1	111	TSH	1
1	Physycian1	111	FT3	0
2	Physycian1	112	FT4	0
3	Physycian1	112	FT3	0
3	Physycian1	113	FT3	0
3	Physycian1	113	FT4	0

And I have query like this:

SELECT  
    physician, 
    COUNT(DISTINCT order_id) sum,
    CASE price 
        WHEN 0 THEN 'Free'
        ELSE 'Paid'
    END AS is_free
FROM
    table
GROUP BY
    physician,
    CASE price 
        WHEN 0 THEN 'Free'
        ELSE 'Paid'
    END

And the result is something like this:

physician	sum	is_free
Physician1	3	Free
Physician1	1	Paid

How can I change this query to count order_id=111(and others that might appear in DB) only as Paid and not as both.

So the end result would be:

physician	sum	is_free
Physician1	2	Free
Physician1	1	Paid

CodePudding user response：

First boil your data down to only one row per physician and order. Then count.

select physician, pay_mode, count(*)
from
(
  select
    physician,
    order_id,
    max(case when price = 0 then 'free' else 'paid' end) as pay_mode
  from mytable
  group by physician, order_id
) orders
group by physician, pay_mode
order by physician, pay_mode;