Group-by and coalesce cannot be combined, gives an error?

I am creating an SSRS report and while making the data queries to it I got into this error. I do need the data set (Month to Date and Year to Date values) in one row connected with a group by command. This is the crystal report I’m referring to and converting to SSRS.

The error I get is something like : Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Note: I cannot use 2 sub queries such as one for MTD and one for YTD values since values does not match with the row description. That is the whole reason I’m trying to use some coalesce functions with individual group-by commands.

What I have tried :

SET ARITHABORT OFF
SET ANSI_WARNINGS OFF
SELECT coalesce((Select ((SUM( "IS_vwSalesbyProductLineComp"."sls_amt")-SUM( "IS_vwSalesbyProductLineComp"."cost_amt"))/SUM( "IS_vwSalesbyProductLineComp"."sls_amt"))*100 ),0) AS G1MTD,
       "IS_vwSalesbyProductLineComp"."Mainslspsn_name",
       "IS_vwSalesbyProductLineComp"."PCSummarized",
       coalesce ((SELECT SUM("IS_vwSalesbyProductLineComp"."qty_to_ship"-"IS_vwSalesbyProductLineComp"."qty_return_to_stk")FROM "100"."dbo"."IS_vwSalesbyProductLineComp"
   where Year = 2021
   and Month between 1 and 12
   Group by Mainslspsn_name,PCSummarized),0) AS Qty_Sold,
       coalesce ((SELECT SUM("IS_vwSalesbyProductLineComp"."sls_amt")FROM "100"."dbo"."IS_vwSalesbyProductLineComp"
   where Year = 2021
   and Month between 1 and 12
   Group by Mainslspsn_name,PCSummarized),0) AS YTD_Sales,
       coalesce ((SELECT SUM("IS_vwSalesbyProductLineComp"."cost_amt")FROM "100"."dbo"."IS_vwSalesbyProductLineComp"
   where Year = 2021
   and Month between 1 and 12
   Group by Mainslspsn_name,PCSummarized),0) AS YTD_COGS,
       coalesce((Select ((SUM( "IS_vwSalesbyProductLineComp"."sls_amt")-SUM( "IS_vwSalesbyProductLineComp"."cost_amt"))/SUM( "IS_vwSalesbyProductLineComp"."sls_amt"))*100 
       FROM "100"."dbo"."IS_vwSalesbyProductLineComp"
   where Year = 2021
   and Month between 1 and 12
   Group by Mainslspsn_name,PCSummarized),0) AS G2YTD,
        SUM("IS_vwSalesbyProductLineComp"."qty_to_ship"-"IS_vwSalesbyProductLineComp"."qty_return_to_stk") 
        AS Qty_Sold,
        SUM("IS_vwSalesbyProductLineComp"."sls_amt") 
        AS MTD_Sales,
        SUM("IS_vwSalesbyProductLineComp"."cost_amt") 
        AS MTD_COGS   
   FROM "100"."dbo"."IS_vwSalesbyProductLineComp"
   where Year = 2021
   and Month = 12
   Group by Mainslspsn_name,PCSummarized
   order by PCSummarized

The issue is with the individual group-by commands following with the coalesce I feel like, because without those the query works but only returns the same total sum value for all the rows. Reference

Here is a sample dataset for reference :

CREATE TABLE IS_vwSalesbyProductLineComp
    ([Mainslspsn_no](int)
      ,[Mainslspsn_name](char(64))
      ,[PCSummarized](char(75))
      ,[Year](int)
      ,[Month](int)
      ,[MonthLit](varchar(3))
      ,[PCSummary](varchar(24))
      ,[slspsn_no](int)
      ,[fullname](char(64))
      ,[cus_no](char(20))
      ,[cus_name](char(50))
      ,[inv_dt](datetime)
      ,[sls_amt](decimal(16,2))
      ,[cost_amt](decimal(16,2))
      ,[item_no](char(30))
      ,[item_desc_1](char(30))
      ,[item_desc_2](char(30))
      ,[prod_cat](char(3))
      ,[prod_cat_desc](char(15))
      ,[qty_ordered](decimal(13,4))
      ,[qty_to_ship](decimal(13,4))
      ,[qty_return_to_stk](decimal(13,4))
      ,[unit_price](decimal(16,6))
      ,[unit_cost](decimal(16,6))
      ,[state](char(3))
      ,[city](varchar(100))
      ,[zip](varchar(20))
      ,[cus_type_cd](char(5))
      ,[loc](char(3)))
;

INSERT INTO IS_vwSalesbyProductLineComp
    ([Mainslspsn_no]
      ,[Mainslspsn_name]
      ,[PCSummarized]
      ,[Year]
      ,[Month]
      ,[MonthLit]
      ,[PCSummary]
      ,[slspsn_no]
      ,[fullname]
      ,[cus_no]
      ,[cus_name]
      ,[inv_dt]
      ,[sls_amt]
      ,[cost_amt]
      ,[item_no]
      ,[item_desc_1]
      ,[item_desc_2]
      ,[prod_cat]
      ,[prod_cat_desc]
      ,[qty_ordered]
      ,[qty_to_ship]
      ,[qty_return_to_stk]
      ,[unit_price]
      ,[unit_cost]
      ,[state]
      ,[city]
      ,[zip]
      ,[cus_type_cd]
      ,[loc])
VALUES
    (849, 'Paul Dean','Band Saw Blades', 2022,3, 'Mar', 'Bandsaw Blades', 849, 'Paul Dean', '123456','ABC PRODUCTS','2022-03-16 00:00:00.000',357.60,288.35,'01583220126','126 X 5/8 X 022 3T PREMIUM',,106,'BLD-5/8 X 022',80.0000,80.0000,0.0000,4.470000,3.604391,'NC','SHELBY','28152','ASII8','FL'),
    (849, 'Paul Dean','Band Saw Blades', 2022,3, 'Mar', 'Bandsaw Blades', 849, 'Paul Dean', '123456','ABC PRODUCTS','2022-03-16 00:00:00.000',357.60,310.57,'01583220142','142 X 5/8 X 022 3T PREMIUM',,106,'BLD-5/8 X 022',80.0000,80.0000,0.0000,4.470000,3.882166,'NC','SHELBY','28152','ASII8','FL'),
    (849, 'Paul Dean','Band Saw Blades', 2022,3, 'Mar', 'Bandsaw Blades', 849, 'Paul Dean', '122334','XYZ PRODUCTS','2022-03-16 00:00:00.000',56.76,33.47,'01583220098','98 X 5/8 X 022 3T PREMIUM',,106,'BLD-5/8 X 022',24.0000,24.0000,0.0000,4.780000,3.117917,'FL','BOYNTON BEACH','33472','CII18','FL'),
    (849, 'Paul Dean','Band Saw Blades', 2022,3, 'Mar', 'Bandsaw Blades', 849, 'Paul Dean', '122334','XYZ PRODUCTS','2022-03-16 00:00:00.000',56.76,74.83,'01583220098','98 X 5/8 X 022 3T PREMIUM',,106,'BLD-5/8 X 022',24.0000,24.0000,0.0000,4.780000,3.117917,'FL','BOYNTON BEACH','33472','CII18','FL'),
    (849, 'Paul Dean','Band Saw Blades', 2022,3, 'Mar', 'Bandsaw Blades', 849, 'Paul Dean', '122334','XYZ PRODUCTS','2022-03-16 00:00:00.000',189.20,127.29,'01583220098','98 X 5/8 X 022 3T PREMIUM',,106,'BLD-5/8 X 022',24.0000,24.0000,0.0000,4.780000,3.117917,'FL','BOYNTON BEACH','33472','CII18','FL'),
    (201, 'John Snider','Band Saw Blades', 2022,3, 'Mar', 'Bandsaw Blades', 201, 'John Snider', '107974','CDE SUPPLIES','2022-03-16 00:00:00.000',205.92,120.87,'01583220098','98 X 5/8 X 022 3T PREMIUM',,106,'BLD-5/8 X 022',24.0000,24.0000,0.0000,4.780000,3.117917,'FL','BOYNTON BEACH','33472','CII18','FL'),
    (201, 'John Snider','Band Saw Blades', 2022,3, 'Mar', 'Bandsaw Blades', 201, 'John Snider', '107974','CDE SUPPLIES','2022-03-16 00:00:00.000',977.60,570.48,'01583220098','98 X 5/8 X 022 3T PREMIUM',,106,'BLD-5/8 X 022',24.0000,24.0000,0.0000,4.780000,3.117917,'FL','BOYNTON BEACH','33472','CII18','FL'),
    (201, 'John Snider','Band Saw Blades', 2022,3, 'Mar', 'Bandsaw Blades', 201, 'John Snider', '107974','CDE SUPPLIES','2022-03-16 00:00:00.000',146.64,86.41,'01583220098','98 X 5/8 X 022 3T PREMIUM',,106,'BLD-5/8 X 022',24.0000,24.0000,0.0000,4.780000,3.117917,'FL','BOYNTON BEACH','33472','CII18','FL'),
    (201, 'John Snider','Band Saw Blades', 2022,3, 'Mar', 'Bandsaw Blades', 201, 'John Snider', '107974','CDE SUPPLIES','2022-03-16 00:00:00.000',293.28,180.30,'01583220098','98 X 5/8 X 022 3T PREMIUM',,106,'BLD-5/8 X 022',24.0000,24.0000,0.0000,4.780000,3.117917,'FL','BOYNTON BEACH','33472','CII18','FL'),
    (201, 'John Snider','Band Saw Blades', 2022,3, 'Mar', 'Bandsaw Blades', 201, 'John Snider', '107974','CDE SUPPLIES','2022-03-16 00:00:00.000',293.28,186.11,'01583220098','98 X 5/8 X 022 3T PREMIUM',,106,'BLD-5/8 X 022',24.0000,24.0000,0.0000,4.780000,3.117917,'FL','BOYNTON BEACH','33472','CII18','FL'),
    (207, 'House Accounts','Band Saw Blades', 2022,3, 'Mar', 'Bandsaw Blades', 207, 'House Accounts', '107974','SUPERIOR PRODUCTS','2022-03-16 00:00:00.000',365.60,215.32,'01583220065','65 X 5/8 X 022 3T PREMIUM',,106,'BLD-5/8 X 022',24.0000,24.0000,0.0000,4.780000,3.117917,'FL','BOYNTON BEACH','33472','CII18','FL'),
    (207, 'House Accounts','Band Saw Blades', 2022,3, 'Mar', 'Bandsaw Blades', 207, 'House Accounts', '107974','SUPERIOR PRODUCTS','2022-03-16 00:00:00.000',365.60,233.08,'0158422008014','80-1/4 X 5/8 X 022 4T PREMIUM',,106,'BLD-5/8 X 022',24.0000,24.0000,0.0000,4.780000,3.117917,'FL','BOYNTON BEACH','33472','CII18','FL'),
    (207, 'House Accounts','Band Saw Blades', 2022,3, 'Mar', 'Bandsaw Blades', 207, 'House Accounts', '107974','SUPERIOR PRODUCTS','2022-03-16 00:00:00.000',1506.00,1099.40,'01583220124','124 X 5/8 X 022 3T PREMIUM',,106,'BLD-5/8 X 022',24.0000,24.0000,0.0000,4.780000,3.117917,'FL','BOYNTON BEACH','33472','CII18','FL'),
    (207, 'House Accounts','Band Saw Blades', 2022,3, 'Mar', 'Bandsaw Blades', 207, 'House Accounts', '107974','SUPERIOR PRODUCTS','2022-03-16 00:00:00.000',502.00,375.76,'01583220126','126 X 5/8 X 022 3T PREMIUM',,106,'BLD-5/8 X 022',24.0000,24.0000,0.0000,4.780000,3.117917,'FL','BOYNTON BEACH','33472','CII18','FL'),
    (207, 'House Accounts','Band Saw Blades', 2022,3, 'Mar', 'Bandsaw Blades', 207, 'House Accounts', '107974','SUPERIOR PRODUCTS','2022-03-16 00:00:00.000',9036.00,6988.46,'0158322012913','129-1/3 X 5/8 X 022 3T PREMIUM',,106,'BLD-5/8 X 022',24.0000,24.0000,0.0000,4.780000,3.117917,'FL','BOYNTON BEACH','33472','CII18','FL')
;

Answer

The solution is to use UNION with a “zeroed” row and add an aggregate :

WITH 
T1 AS
(
SELECT (
       SELECT((SUM(IS_vwSalesbyProductLineComp.sls_amt) - SUM(IS_vwSalesbyProductLineComp.cost_amt)) / SUM(IS_vwSalesbyProductLineComp.sls_amt)) * 100) AS G1MTD, 
       IS_vwSalesbyProductLineComp.Mainslspsn_name, 
       IS_vwSalesbyProductLineComp.PCSummarized, 
       (
       SELECT SUM(IS_vwSalesbyProductLineComp.qty_to_ship - IS_vwSalesbyProductLineComp.qty_return_to_stk)
       FROM "100".dbo.IS_vwSalesbyProductLineComp
       WHERE Year = 2021
             AND Month BETWEEN 1 AND 12
       GROUP BY Mainslspsn_name, 
                PCSummarized) AS Qty_Sold, 
       (
       SELECT SUM(IS_vwSalesbyProductLineComp.sls_amt)
       FROM "100".dbo.IS_vwSalesbyProductLineComp
       WHERE Year = 2021
             AND Month BETWEEN 1 AND 12
       GROUP BY Mainslspsn_name, 
                PCSummarized) AS YTD_Sales, 
      (
       SELECT SUM(IS_vwSalesbyProductLineComp.cost_amt)
       FROM "100".dbo.IS_vwSalesbyProductLineComp
       WHERE Year = 2021
             AND Month BETWEEN 1 AND 12
       GROUP BY Mainslspsn_name, 
                PCSummarized) AS YTD_COGS, 
       (
       SELECT((SUM(IS_vwSalesbyProductLineComp.sls_amt) - SUM(IS_vwSalesbyProductLineComp.cost_amt)) / SUM(IS_vwSalesbyProductLineComp.sls_amt)) * 100
       FROM "100".dbo.IS_vwSalesbyProductLineComp
       WHERE Year = 2021
             AND Month BETWEEN 1 AND 12
       GROUP BY Mainslspsn_name, 
                PCSummarized) AS G2YTD, 
       SUM(IS_vwSalesbyProductLineComp.qty_to_ship - IS_vwSalesbyProductLineComp.qty_return_to_stk) AS Qty_Sold2, --> renamed because ambigous
       SUM(IS_vwSalesbyProductLineComp.sls_amt) AS MTD_Sales, 
       SUM(IS_vwSalesbyProductLineComp.cost_amt) AS MTD_COGS
FROM "100".dbo.IS_vwSalesbyProductLineComp
WHERE Year = 2021
      AND Month = 12
GROUP BY Mainslspsn_name, 
         PCSummarized
),
T2 AS 
(
SELECT 0 AS C1, '' AS C2, '' AS C3, 0 AS C4, 0 AS C5, 0 AS C6, 0 AS C7, 0 AS C8, 0 AS C9, 0 AS C10
),
T3 AS
(
SELECT * FROM T1
UNION ALL
SELECT * FROM T2
)
SELECT SUM(G1MTD) AS G1MTD, 
       STRING_AGG(Mainslspsn_name, '') AS Mainslspsn_name,
       STRING_AGG(PCSummarized, '') AS PCSummarized,
       SUM(Qty_Sold) AS Qty_Sold,
       SUM(YTD_Sales) AS YTD_Sales,
       SUM(YTD_COGS) AS YTD_COGS,
       SUM(G2YTD) AS G2YTD,
       SUM(Qty_Sold2) AS Qty_Sold2,
       SUM(MTD_Sales) AS MTD_Sales,
       SUM(MTD_COGS) AS MTD_COGS
FROM T3
ORDER BY PCSummarized;