Subquery from another table in Case when condition

Question

I am trying to write a query where I want to sum a price column based on the condition which is a subquery.

my query :

select
  fund.FundName, 
  SUM(Case when (
        Select Top 1 bitValue 
        from table 1 
        where table1.id = Company.id and table1.field = 25
        ) = 1 then price else 0 end) as 'TotalPrice'
from
Fund left outer join Company on Company.fundId=fund.id 
group by
fund.fundName

It throws me error : Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

What is the best alternative way to achieve this.

Does `Top 1` exist in MySQL? I think you got the database tag wrong. This may be Sybase or SQL Server. — The Impaler, Apr 24 '20 at 14:57
Does this answer your question? [SQL Server "cannot perform an aggregate function on an expression containing an aggregate or a subquery", but Sybase can](https://stackoverflow.com/questions/15751241/sql-server-cannot-perform-an-aggregate-function-on-an-expression-containing-an) — forpas, Apr 24 '20 at 15:07
No, it’s a different problem. I have used join outside of this sub query and didn’t work. More over I’m using Top 1 which I didn’t find answer anywhere. I know case doesn’t allow multiple return values. So I was wondering what’s the alternative approach to achieve this scenario. — Urvashi Sharma, Apr 24 '20 at 15:31
Your outer join is unnecessary - there is a 1:1 relationship betwen Fund and Company. Attempting a join for the purpose of the subquery doesn't make much sense. Using TOP without an order by clause is usually a kludge to cover up a logic problem. I can't imagine tables named Company or Fund having a column named Price - so it is difficult to understand your schema and goal. Creating GOOD aliases for your objects and referencing every column with the appropriate alias will make understanding your query easier. Give it a try. — SMor, Apr 24 '20 at 15:40

score 0 · Answer 1 · answered Apr 24 '20 at 15:41

Hope this Works for your Case:

SELECT FUND.FUNDNAME, S.TotalPrice FROM FUND LEFT OUTER JOIN COMPANY ON COMPANY.FUNDID=FUND.ID LEFT JOIN (SELECT CASE WHEN BITVALUE=1 THEN SUM(PRICE) ELSE 0 END as 'TotalPrice',table1.ID from table 1 where table1.id = Company.id and table1.field = 25 GROUP BY table1.ID ) S ON S.ID=Company.id GROUP BY FUND.FUNDNAME

score 0 · Answer 2 · answered Apr 24 '20 at 15:51

untested obviously with no sample data provided.

select fund.FundName
  ,SUM(Case when table1.id is not null then price else 0 end) as 'TotalPrice'
from Fund
left outer join Company on Company.fundId = fund.id
left outer join (
    select distinct id
    from table1
    where field = 25
        and bitvalue = 1
) table1 on table1.id = Company.id
group by fund.fundName

Subquery from another table in Case when condition

2 Answers2