none
Getting a Sum from Case Statement RRS feed

  • Question

  • Hello - I am getting this error when trying to execute my query.  Below is sample DDL that illustrates my query etc.  How do I need to change it so that the query executes as I expect.

    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    Create table customer
    (
        armasterid varchar(100)
       ,arcustname varchar(500)
    )
    Insert Into customer (armasterid, arcustname) Values
    ('AAA', 'Customer A'), ('BBB', 'Customer B'), ('CCC', 'Customer C')
    
    Create table comisDist
    (
       salescategory int
      ,amount decimal
      ,invoice varchar(100)
    )
    Insert Into comisDist (salescategory, amount, invoice) Values
    (5017, '16.15', '1111'), (5017, '22.00', '2222'), (5018, '1000.00', '3333')
    
    Create table comisCat
    (
      arsalescatid int
      ,arslscatname varchar(100)
      ,arsr int
    )
    Insert Into comisCat (arsalescatid, arslscatname, arsr) Values
    (5017, 'Web', 1), (5018, 'Store', 0)
    
    Create table custInvoice
    (
       masterID int
      ,autoinc int
      ,doi date
    )
    Insert Into custInvoice (masterID, autoinc, doi) Values
    (8111, 1111, '2019-01-04'), (7711, 2222, '2019-01-04'), (9911, 3333, '2019-01-04')
    
    Create table hib
    (
        masterID int
    )
    Insert Into hib (masterID) Values
    (8111), (7711), (9911)
    
    Select
    cm.arcustname
    ,Total = (Select
              case cc.arsr
                when 1
                then sum(round(cd.amount,2))
                else Null
               end
              From comisDist cd
              left join comisCat cc
              on cd.salescategory = cc.arsalescatid
              inner join custInvoice ci
              on cd.invoice = ci.autoinc
              inner join hib hb
              on ci.masterid = hb.masterid
              where ci.doi between '2019-01-01' And '2019-01-20'
              Group By cc.arsr)
    From customer cm

    Friday, January 10, 2020 1:59 PM

Answers

  • Is there a way it can be done with only the select from customer like I had, and all other joins were performed in the Total = line?
    	Select
    cm.arcustname
    ,Total = (Select
              case cc.arsr
                when 1
                then sum(round(cd.amount,2))
                else Null
               end
              From comisDist cd
              left join comisCat cc
              on cd.salescategory = cc.arsalescatid
              inner join custInvoice ci
              on cd.invoice = ci.autoinc
              inner join hib hb
              on ci.masterid = hb.masterid
              where ci.doi between '2019-01-01' And '2019-01-20'
    		  AND cm.armasterid =hb.ssID
              Group By cc.arsr)
    From customer cm


    • Marked as answer by IndigoMontoya Friday, January 10, 2020 3:41 PM
    Friday, January 10, 2020 3:35 PM
    Moderator

All replies

  • The following will work but is it what you want?

    Select
    cm.arcustname
    ,Total = ( Select top 1
              case cc.arsr
                when 1
                then sum(round(cd.amount,2))
                else Null
               end
              From comisDist cd
              left join comisCat cc
              on cd.salescategory = cc.arsalescatid
              inner join custInvoice ci
              on cd.invoice = ci.autoinc
              inner join hib hb
              on ci.masterid = hb.masterid
              where ci.doi between '2019-01-01' And '2019-01-20'
              Group By cc.arsr 
    		  order by sum(round(cd.amount,2)) )
    From customer cm

    Friday, January 10, 2020 2:13 PM
    Moderator
  • No - it's ignoring the 1,000 for invoice 3333 - it's only summing 1111 and 2222.
    Friday, January 10, 2020 2:17 PM
  • What is your expected result from your sample?
    Friday, January 10, 2020 2:22 PM
    Moderator
  • Your tables don't have any link between customer and invoices??  The value is simply going to be the same for all customers.  Is that really what you want?

    Friday, January 10, 2020 2:31 PM
    Moderator
  • What is your expected result from your sample?

    I'm trying to take this CTE that shows a breakdown by masterID and flip it so it shows by customer.  For example, this query (same ddl) 

    ;With Data As
    (
        Select
        Total = Sum (case when cc.arsr = 1 then round(cd.amount,2) else null end)
        ,hb.masterid
        From comisDist cd
        left join comisCat cc on cd.salescategory = cc.arsalescatid
        inner join custInvoice ci on cd.invoice = ci.autoinc
        inner join hib hb on hb.masterid = ci.masterid
        where ci.doi between '2019-01-01' And '2019-01-20'
        Group By hb.masterid
    )
    Select * from Data

    would give you the below which is what I expect.  How can I take my query (w/o the CTE or an additional join) and get the same output but by customer? 

    Total masterid
    22 7711
    16 8111
    (null) 9911

    Friday, January 10, 2020 2:38 PM
  • You need a table to link masterid with customer.
    Friday, January 10, 2020 2:43 PM
    Moderator
  • customer joins hib on ssID - would that work since that joins the two tables together?
    Friday, January 10, 2020 2:46 PM
  • customer joins hib on ssID - would that work since that joins the two tables together?
    Where is ssID in table hib or Customer?
    Friday, January 10, 2020 2:54 PM
    Moderator
  • Updated DDL to illustrate.

    Create table customer
    (
        armasterid varchar(100)
       ,arcustname varchar(500)
    )
    Insert Into customer (armasterid, arcustname) Values
    ('AAA', 'Customer A'), ('BBB', 'Customer B'), ('CCC', 'Customer C')
    
    Create table comisDist
    (
       salescategory int
      ,amount decimal
      ,invoice varchar(100)
    )
    Insert Into comisDist (salescategory, amount, invoice) Values
    (5017, '16.15', '1111'), (5017, '22.00', '2222'), (5018, '1000.00', '3333')
    
    Create table comisCat
    (
      arsalescatid int
      ,arslscatname varchar(100)
      ,arsr int
    )
    Insert Into comisCat (arsalescatid, arslscatname, arsr) Values
    (5017, 'Web', 1), (5018, 'Store', 0)
    
    Create table custInvoice
    (
       masterID int
      ,autoinc int
      ,doi date
    )
    Insert Into custInvoice (masterID, autoinc, doi) Values
    (8111, 1111, '2019-01-04'), (7711, 2222, '2019-01-04'), (9911, 3333, '2019-01-04')
    
    Create table hib
    (
        masterID int
        ,ssID varchar(100)
    )
    Insert Into hib (masterID) Values
    (8111, 'AAA'), (7711, 'BBB'), (9911, 'CCC')
    

    Friday, January 10, 2020 2:57 PM
  • --Create table hib
    --(
    --    masterID int
    --    ,ssID varchar(100)
    --)
    --Insert Into hib (masterID,ssID) Values
    --(8111, 'AAA'), (7711, 'BBB'), (9911, 'CCC')
    Select
        Total = Sum (case when cc.arsr = 1 then round(cd.amount,2) else null end)
        ,c.arcustname 
        From comisDist cd
        left join comisCat cc on cd.salescategory = cc.arsalescatid
        inner join custInvoice ci on cd.invoice = ci.autoinc
        inner join hib hb on hb.masterid = ci.masterid
    	join customer c on c.armasterid =hb.ssID 
        where ci.doi between '2019-01-01' And '2019-01-20'
        Group By c.arcustname

    Friday, January 10, 2020 3:21 PM
    Moderator
  • Is there a way it can be done with only the select from customer like I had, and all other joins were performed in the Total = line?
    Friday, January 10, 2020 3:27 PM
  • Is there a way it can be done with only the select from customer like I had, and all other joins were performed in the Total = line?
    	Select
    cm.arcustname
    ,Total = (Select
              case cc.arsr
                when 1
                then sum(round(cd.amount,2))
                else Null
               end
              From comisDist cd
              left join comisCat cc
              on cd.salescategory = cc.arsalescatid
              inner join custInvoice ci
              on cd.invoice = ci.autoinc
              inner join hib hb
              on ci.masterid = hb.masterid
              where ci.doi between '2019-01-01' And '2019-01-20'
    		  AND cm.armasterid =hb.ssID
              Group By cc.arsr)
    From customer cm


    • Marked as answer by IndigoMontoya Friday, January 10, 2020 3:41 PM
    Friday, January 10, 2020 3:35 PM
    Moderator