CODEWITHSUNDEEP
sqlsql-serversyntaxsubquery
Bevan
Bevan

Reputation: 584

the subquery returned more than 1 value

I'm trying to make a 'sales performance' table in html. Here's the query:

select DR.ACCNO, DR.NAME, DR.X_MONTHLYTARGET, DR.AGEDBAL0, DR.LASTMONTH, DR.MONTHVAL,
    (select SUM(ASI.SalesValueTaxExclusive) 
from dbo.DR_ACCS DR     
        inner join dbo.Analytics_SaleInvoice ASI on DR.ACCNO = ASI.AccountNumberAnalysis
     where ASI.PeriodID = 229      and DR.X_TOPCUSTOMER = 'Y' 
    group by DR.ACCNO) as nov    

from dbo.DR_ACCS DR    
where X_TOPCUSTOMER = 'Y'   
group by DR.ACCNO, DR.NAME, DR.X_MONTHLYTARGET, DR.AGEDBAL0, DR.LASTMONTH, DR.MONTHVAL

Which just falls over saying more than 1 result. I've tried to make head/tail of other posts but I'm lost.

Any help appreciated.

Upvotes: 1

Views: 61

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

The issue is this subquery:

(select SUM(ASI.SalesValueTaxExclusive) 
 from dbo.DR_ACCS DR inner join
      dbo.Analytics_SaleInvoice ASI
      on DR.ACCNO = ASI.AccountNumberAnalysis
 where ASI.PeriodID = 229  and DR.X_TOPCUSTOMER = 'Y' 
 group by DR.ACCNO
-^
) as nov

As highlighted, the problem is the group by. Instead, you want a correlated subquery:

(select SUM(ASI.SalesValueTaxExclusive) 
 from dbo.Analytics_SaleInvoice ASI
 where DR.ACCNO = ASI.AccountNumberAnalysis and
       ASI.PeriodID = 229  and DR.X_TOPCUSTOMER = 'Y' 
) as nov

This removes the unnecessary table reference to DR_ACCS in the subquery. So, now the condition refers to the table in the outer query. With this condition, you don't need a group by clause. The subquery only aggregates one account at a time.

EDIT:

This full query should work:

select DR.ACCNO, DR.NAME, DR.X_MONTHLYTARGET, DR.AGEDBAL0, DR.LASTMONTH, DR.MONTHVAL,
       (select SUM(ASI.SalesValueTaxExclusive) 
        from dbo.Analytics_SaleInvoice ASI
        where DR.ACCNO = ASI.AccountNumberAnalysis and
              ASI.PeriodID = 229 and
       ) as nov 
from dbo.DR_ACCS DR    
where X_TOPCUSTOMER = 'Y'   
group by DR.ACCNO, DR.NAME, DR.X_MONTHLYTARGET, DR.AGEDBAL0, DR.LASTMONTH, DR.MONTHVAL;

The condition DR.X_TOPCUSTOMER = 'Y' is redundant in the subquery, so I removed it.

Upvotes: 1

pawinder gupta
pawinder gupta

Reputation: 1265

You are using group by in subquery. Subquery should not return more than one row. If you remove group by clause from subquery it should work.

Upvotes: 1

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

Which clearly says that multiple rows are returned from this query because of

    (select SUM(ASI.SalesValueTaxExclusive) 
     from dbo.DR_ACCS DR     
     inner join dbo.Analytics_SaleInvoice ASI on DR.ACCNO = ASI.AccountNumberAnalysis
     where ASI.PeriodID = 229      and DR.X_TOPCUSTOMER = 'Y' 
     group by DR.ACCNO) as nov

Upvotes: 0

Related Questions