GROUP BY in subquery T-SQL

Question

I am trying to make a table like this:

ProductName | SalesByDate | TotalSalesUntilDate

    A     |      5    |      15

    B     |     10    |      30

    C     |     20    |      25

    D     |     18    |      43

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    |     5     |      113

     B    |    10     |      113

     C    |    20     |      113

     D    |    18     |      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.

Answer 1

In the @Date variable we are storing the date:

SELECT DISTINCT PT.[ProductName]
      ,SUM(IIF(PD.[Date] = @Date, SL.[SalesByDate], 0)) 
      ,SUM(IIF(PD.[Date] BETWEEN '01' AND @Date, SL.[SalesByDate], 0)) 
FROM @Product PT
INNER JOIN @Sales SL
    ON PT.[ProductID] = SL.[ProductID]
INNER JOIN @Period PD
    ON SL.[TimeID] = PD.[TimeID]
GROUP BY PT.[ProductName]

Result:

Full code:

DECLARE @Period TABLE
(
     [TimeID] TINYINT
    ,[Date] CHAR(2)
)

INSERT INTO @Period([TimeID], [Date])
VALUES   (1,'01')
        ,(2,'02')
        ,(3,'03')
        ,(4,'04')
        ,(5,'05')
        ,(6,'06')
        ,(7,'07')
        ,(8,'08')
        ,(9,'09')
        ,(10,'10')
        ,(11,'11')
        ,(12,'12')
        ,(13,'13')
        ,(14,'14')
        ,(15,'15')

DECLARE @Product TABLE
(
     [ProductID] TINYINT
    ,[ProductName] CHAR(1)
)

INSERT INTO @Product( [ProductID], [ProductName])
VALUES (1,'A')
      ,(2,'B')
      ,(3,'C')
      ,(4,'D')

DECLARE @Sales TABLE
(
     [TimeID] TINYINT
    ,[ProductID] TINYINT
    ,[SalesByDate] TINYINT
)

INSERT INTO @Sales ([TimeID], [ProductID], [SalesByDate])
VALUES   (1, 1, 10)
        ,(1, 4, 20)
        ,(7, 2, 10)
        ,(7, 3, 5)
        ,(15, 1, 5)
        ,(15, 2, 10)
        ,(15, 3, 15)
        ,(15, 4, 18)
        ,(19, 2, 15)
        ,(20, 3, 2)
        ,(22, NULL, 2)
        ,(1, 4, 5)
        ,(7, 2, 10)
        ,(15, 3, 5)

DECLARE @Date CHAR(2) = '15'

SELECT DISTINCT PT.[ProductName]
      ,SUM(IIF(PD.[Date] = @Date, SL.[SalesByDate], 0)) 
      ,SUM(IIF(PD.[Date] BETWEEN '01' AND @Date, SL.[SalesByDate], 0)) 
FROM @Product PT
INNER JOIN @Sales SL
    ON PT.[ProductID] = SL.[ProductID]
INNER JOIN @Period PD
    ON SL.[TimeID] = PD.[TimeID]
GROUP BY PT.[ProductName]

EDIT:

If you need to use sub-query, this is how your example can works:

SELECT PT.[ProductName]
      ,SUM(SL.[SalesByDate])
      ,DataSource.[TotalSalesByDate]
FROM @Product PT
INNER JOIN @Sales SL
    ON PT.[ProductID] = SL.[ProductID]
INNER JOIN @Period PD
    ON SL.[TimeID] = PD.[TimeID]
INNER JOIN 
(
    SELECT S.[ProductID]
          ,SUM(S.[SalesByDate]) AS [TotalSalesByDate]
    FROM @Sales S
    INNER JOIN @Period P
        ON S.[TimeID] = P.[TimeID]
    WHERE P.[Date] BETWEEN '01' AND @Date 
    GROUP BY S.[ProductID] 
) AS DataSource
ON PT.[ProductID] = DataSource.[ProductID]
WHERE PD.[Date] = @Date
GROUP BY PT.[ProductName]
     ,DataSource.[TotalSalesByDate]

Answer 2

First, in the Table Period you must have dates, not '01','02' so you can use BETWEEN. Or you can use 1,2,3 ... but they have to be numbers.

So, we suppose that in table Table Period you have numbers for dates (I make this remark, because you use 01, instead of 1 which assumes string value. The query itself is relatively easy:

SELECT 
  p.ProductName, 
  SUM(CASE WHEN s.TimeID = 10 THEN s.SalesByDate ELSE 0 END) as SalesByDate, 
  SUM(CASE WHEN s.TimeID = 10 THEN 0 ELSE s.SalesByDate END) as TotalSalesUntilDate 
FROM 
  Product p 
  INNER JOIN Salse s ON p.ProductID = s.ProductID 
WHERE 
  s.TimeID BETWEEN 1 AND 10
GROUP BY p.ProductName;

You take Sales for each date. If this is a selected date then add sales to column SalesByDate, else add then to column TotalSalesUntilDate. You group by ProductName to calculate SUM. And select only dates which are in the desired period in WHERE clause. We assume that this query is started only for a specific month (because we use only date element - i.e. 1,2,... not month).

This will show only Products with sales. If you want to see all Products list use LEFT JOIN instead of INNER JOIN.