CODEWITHSUNDEEP
t-sqlsql-server-2008-r2
d90
d90

Reputation: 787

Summing Subquery

I'm trying to create a Query that sums up a sales amount from 52 weeks of data from the entities in this table.

enter image description here

I figured out how to get the past 52 weeks for the CYSales using the following query. The year and week will end up being parameters in the resultant report, so those values are arbitrary. I used the current year and week 40 just for this example. 

SELECT  [Group],[Owner],[SalesPersonNumber],SUM([tot_sls_amt]) AS CYSales
  FROM TableA
  WHERE ((Year = year(getdate()) AND Week <= 40) OR (Year = year(getdate()) - 1 AND Week > 40)) AND cmp_status = 'A'
  Group BY [Group] 
      ,[Owner]
      ,[SalesPersonNumber]

That gives me this

enter image description here

I'm now trying to get the PYSales amount in another calculated column, but when I add my subquery in I get an error 

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

How do I fix the following query to give me the PYSales? 

SELECT  [Group]
        ,[Owner]
        ,[SalesPersonNumber]
        ,SUM([tot_sls_amt]) AS CYSales
        ,(SELECT SUM([tot_sls_amt]) 
          FROM TableA
          WHERE ((Year = year(getdate())-1 AND Week <= 40) OR (Year = year(getdate()) - 2 AND Week > 40)) AND cmp_status = 'A'
          Group BY [Group] ,[Owner],[SalesPersonNumber]) AS PYSales
  FROM TableA
  WHERE ((Year = year(getdate()) AND Week <= 40) OR (Year = year(getdate()) - 1 AND Week > 40)) AND cmp_status = 'A'
  Group BY [Group] 
          ,[Owner]
          ,[SalesPersonNumber]

Upvotes: 0

Views: 51

Answers (2)

lookslikeanevo
lookslikeanevo

Reputation: 565

I would move your sub query and join it or use a cte.

i used a left join incase you have someone new that wont have data in py

didnt have data to test, let me know if something fails and i can revise. 

SELECT  

a.[Group]
,a.[Owner]
,a.[SalesPersonNumber]
,SUM(a.[tot_sls_amt]) AS CYSales
,(b.[tot_sls_amt]) AS PYSales

  FROM 

TableA a
        Left Join 
            (SELECT sum([tot_sls_amt]) tot_sls_amt,[Group],[Owner] ,[SalesPersonNumber]
                 FROM TableA
                     WHERE ((Year = year(getdate())-1 AND Week <= 40) OR (Year = year(getdate()) - 2 AND Week > 40)) AND cmp_status = 'A'
                        group by [Group],[Owner] ,[SalesPersonNumber]) b on a.group = b.group and a.owner = b.owner and a.salespersonumber =b.salespersonnumber



WHERE 
((Year = year(getdate()) AND Week <= 40) OR (Year = year(getdate()) - 1 AND Week > 40)) AND cmp_status = 'A'


Group BY 
a.[Group] 
,a.[Owner]
,a.[SalesPersonNumber]
,(b.[tot_sls_amt])

Upvotes: 1

radar
radar

Reputation: 13425

you are doing GROUP BY in the subquery, which will return sum for each pair of Group, Owner, SalesPersonNumber, which is same as your outer query.

in case you just need the Total accross the all values for given week and year, just do sum and use cross apply

SELECT  [Group]
        ,[Owner]
        ,[SalesPersonNumber]
        ,SUM([tot_sls_amt]) AS CYSales,
        T.total AS PYSales
  FROM TableA
  WHERE ((Year = year(getdate()) AND Week <= 40) OR (Year = year(getdate()) - 1 AND Week > 40)) AND cmp_status = 'A'
  Group BY [Group] 
          ,[Owner]
          ,[SalesPersonNumber]
  CROSS APPLY (
          SELECT SUM([tot_sls_amt])  as total
          FROM TableA
          WHERE ((Year = year(getdate())-1 AND Week <= 40) OR (Year = year(getdate()) - 2 AND Week > 40)) AND cmp_status = 'A'
          ) As T

Upvotes: 0

Related Questions