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')
;
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;