Can I multiply the result of a SELECT query by an integer within a WHERE clause?-CodePudding

In this problem, I am finding musicians who play 'many' instruments - i.e. more than half of the total number of instruments.

I have a query to retrieve the total number of instruments. I cannot hardcode this value (e.g. using num_instruments > 6). I must use the query to retrieve the number of instruments and remove all doubt.

create or replace view instrument_count
as
select count(distinct instrument)
from instrument_list;

Here is what my query looks like:

-- Which performers play more than half of the total instruments? 
create or replace view multi_performers
as
select *
from performers_instruments
where n_instruments > ((1/2) * (select count from instrument_count))
;

However, instead of returning values above 6, it returns regular values. It seems to have no effect. OUTPUT:

music=# select * from multi_performers limit 10;
 performer | n_instruments 
----------- ---------------
         1 |             2
         3 |             1
         4 |             2
         5 |             3
         6 |             6
         7 |             5
         8 |             5
         9 |             1
        10 |             6
        11 |             3

When I do NOT multiply the select statement, it acts normally - i.e. if the subquery returns '12', then it will return no values as nobody plays more than the maximum number of instruments (which is 12).

-- Which performers play more than half of the total instruments? 
create or replace view multi_performers
as
select *
from performers_instruments
where n_instruments > (select count from instrument_count)
;

OUTPUT:

music=# select * from multi_performers limit 10;
 performer | n_instruments 
----------- ---------------
(0 rows)

Any idea how to implement the requirement of 'more than half'? I.e. how to multiply the result of a subquery and use it within a larger query.

CodePudding user response：

Integer division.

SELECT 1/2;  -- 0 !

Try instead:

select *
from   performers_instruments
where  n_instruments > (select count(distinct instrument) * 0.5 from instrument_list);

Also cutting out the view instrument_count, which seems unnecessary.