none
GROUP BY cause in T-SQL subquery

    Question

  • Hi,

    I am trying to make a table like this:

    ProductName |  SalesByDate  |  TotalSalesUntilDate

    A  |             4  | 29

    B  | 9  |              30

    C  | 10  | 14

    SalesByDate means the number of product sold for each product on the input date and TotalSalesUntilDate indicates the number of product sold for each product from the first date of the month until the input date (example of input date: 17 March 2010)

    I wrote this query using subquery:

    select p.ProductName, A.SalesByDate,(select(SUM(case when (pd.Date between '01' and @Date) then s.SalesByDate else 0 end))
    from Period pd inner join Sales s on pd.TimeID = s.TimeID full join Product p on s.ProductID = p.ProductID)as TotalSalesUntilDate
    
    from Product p join 
                    (select s.ProductID, pd.Date, s.SalesByDate
    		 from Period pd join Sales s on pd.TimeID = s.TimeID) A on p.ProductID = A.ProductID
    
    where @Date = A.Date


    but I got the result:

    ProductName   |     SalesByDate  |  TotalSalesUntilDate

    A  |             4  |           113

    B  |             9  |           113

    C  | 10  |           113

    which the TotalSalesUntilDate shows the number of product sold from the first date of the month until the input date but for all product without separation for each product.

    So when I tried to change the query to like this (adding group by p.ProductID before as TotalSalesUntilDate):

    select p.ProductName, A.SalesByDate,(select(SUM(case when (pd.Date between '01' and @Date) then s.SalesByDate else 0 end))
    from Period pd inner join Sales s on pd.TimeID = s.TimeID full join Product p on s.ProductID = p.ProductID group by p.ProductID)as TotalSalesUntilDate
    
    from Product p join 
                    (select s.ProductID, pd.Date, s.SalesByDate
    		 from Period pd join Sales s on pd.TimeID = s.TimeID) A on p.ProductID = A.ProductID
    
    where @Date = A.Date


    and when I execute this query, I got this error message:

    "Msg 512, Level 16, State 1, Procedure SalesMTDSubQuery, Line 7
    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

    Since I'm new in SQL and still learning, but I don't understand how to solve this. Any help will be appreciated. Thank you.


    • Edited by creamcouple Monday, November 11, 2013 3:34 AM
    Monday, November 11, 2013 3:29 AM

Answers

  • Hi creamcouple Welcome to the forum. The problem in your query subquery returns more than one value since you grouped the result by Product. However you used the expression in the SELECT column list that expects a scalar value. Move the subquery from the SELECT list to a derived table expression and INNER JOIN with the Product table like below

    select 
    	p.ProductName, A.SalesByDate,
    	T.TotalSalesUntilDate
    from Product p join 
             (
    		 select s.ProductID, pd.Date, s.SalesByDate
    		 from Period pd join Sales s on pd.TimeID = s.TimeID
    		 ) A on p.ProductID = A.ProductID
    INNER JOIN
    	(
    		select p.ProductID, (SUM(case when (pd.Date between '01' and @Date) then s.SalesByDate else 0 end)) AS TotalSalesUntilDate
    		from 
    			Period pd 
    		inner join Sales s on pd.TimeID = s.TimeID 
    		full join Product p on s.ProductID = p.ProductID group by p.ProductID
    		) T
    ON T.ProductID = p.ProductID
    where A.Date = @Date

    Try this. Please provide table structure, sample data  and expected output whenever posting questions in the forum so that we can help you out without making guess.


    Krishnakumar S

    Monday, November 11, 2013 4:00 AM

All replies

  • Hi creamcouple Welcome to the forum. The problem in your query subquery returns more than one value since you grouped the result by Product. However you used the expression in the SELECT column list that expects a scalar value. Move the subquery from the SELECT list to a derived table expression and INNER JOIN with the Product table like below

    select 
    	p.ProductName, A.SalesByDate,
    	T.TotalSalesUntilDate
    from Product p join 
             (
    		 select s.ProductID, pd.Date, s.SalesByDate
    		 from Period pd join Sales s on pd.TimeID = s.TimeID
    		 ) A on p.ProductID = A.ProductID
    INNER JOIN
    	(
    		select p.ProductID, (SUM(case when (pd.Date between '01' and @Date) then s.SalesByDate else 0 end)) AS TotalSalesUntilDate
    		from 
    			Period pd 
    		inner join Sales s on pd.TimeID = s.TimeID 
    		full join Product p on s.ProductID = p.ProductID group by p.ProductID
    		) T
    ON T.ProductID = p.ProductID
    where A.Date = @Date

    Try this. Please provide table structure, sample data  and expected output whenever posting questions in the forum so that we can help you out without making guess.


    Krishnakumar S

    Monday, November 11, 2013 4:00 AM
  • Try this,

    select p.ProductName, A.SalesByDate,
    (select(SUM(case when (pd.Date between '01' and @Date) then s.SalesByDate else 0 end))
    from Period pd inner join Sales s on pd.TimeID = s.TimeID full join Product p1 on s.ProductID = p1.ProductID 
    group by p1.ProductID where p1.ProductID = p.ProductID)as TotalSalesUntilDate
    from Product p join 
    (select s.ProductID, pd.Date, s.SalesByDate
    from Period pd join Sales s on pd.TimeID = s.TimeID) A on p.ProductID = A.ProductID
    where @Date = A.Date

    OR

    select p.ProductName, A.SalesByDate, 
    (select(SUM(case when (pd.Date between '01' and @Date) then s.SalesByDate else 0 end))
    from Period pd inner join Sales s on pd.TimeID = s.TimeID full join Product p1 on s.ProductID = p1.ProductID 
    group by p1.ProductID where p1.ProductID = p.ProductID) as TotalSalesUntilDate
    from Product p inner join Sales s on p.ProductID = s.ProductID
    inner join Period pd on s.TimeID = pd.TimeID
    where pd.Date = @Date


    Regards, RSingh


    • Edited by RSingh() Monday, November 11, 2013 4:04 AM
    Monday, November 11, 2013 4:00 AM
  • I think u can get desired results without using subqueries!!

    select  P.ProductID
    ,		p.ProductName
    ,	    sum(case when pd.Date = @Date then s.SalesByDate) 
    ,	    sum(case when (pd.Date between '01' and @Date) then s.SalesByDate else 0 end)	
    from period pd 
    inner join Sales s on pd.TimeID = s.TimeID 
    full join Product p on s.ProductID = p.ProductID
    where @Date between '01' and @Date
    group by p.ProductID


    ESHANI. Please click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

    Monday, November 11, 2013 4:38 AM
  • Can you post sample data + desired result?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, November 11, 2013 6:49 AM
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

    This is minimal polite behavior on SQL forums. What little you did post makes no sense.Your "Product" says that you have only one product; an SQL programmer would have a collective or plural name for this table. 

    DATE is a reserved word and too vague to be data element name. 

    "Period" is too vague to be data element name. 

    The data element name "time_id" is crazy! Time is a scale, a measurement. Do you also have a "liter_id", "meter_id" or "voltage_id" too? 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Tuesday, November 12, 2013 1:15 AM