I don't get how to make a subquery with aggregate functions, about how to handle the aggregate functions and use it with others.
This is my code
SET DATEFORMAT DMY;
GO
SELECT
NumeroD AS 'Bill '
, Descrip1 AS 'Product Description'
, MAX(ExistAnt) AS 'Ex. Act.'
, MIN((ExistAnt)) AS 'Ex. Act.'
, SUM(CASE
WHEN TipoFac = 'A' THEN Amount
WHEN TipoFac = 'B' THEN -Amount END) AS 'Total Amount'
, CASE
WHEN TipoFac = 'A' THEN (Amount*Price) END AS 'Total Sale'
, CASE
WHEN TipoFac = 'A' THEN (Amount*Cost) END AS 'Total Cost'
, CASE
WHEN TipoFac = 'A' THEN 'Total Sale' - 'Total Cost' END AS 'Total Utility'
, CASE
WHEN TipoFac = 'A' THEN ('Total Utility' * 100) / 'Total Sale' END AS '% Util'
, NroLineaC
, DEsComp
FROM
SAITEMFAC
WHERE
DateE BETWEEN '05/07/2022' AND '05/07/2022 23:59:59'
AND codubic = '0010-01'
AND CodItem = '11001'
GROUP BY
Coditem
, NumeroD
, Descrip1
, TipoFac
, Cantidad
, Precio
, Costo
When I click the F5 key, I get an error:
The data types varchar and varchar are incompatible in the subtract operator
Could somebody please guide me about what steps to follow?
I appreciate your help.
PS: maybe using a CTE?
CodePudding user response：
A simple approach is to re-type your logic instead of using aliases (which won't work without a CTE or derived table), ie replace this:
, CASE
WHEN TipoFac = 'A' THEN (Amount*Price) END AS 'Total Sale'
, CASE
WHEN TipoFac = 'A' THEN (Amount*Cost) END AS 'Total Cost'
, CASE
WHEN TipoFac = 'A' THEN 'Total Sale' - 'Total Cost' END AS 'Total Utility'
With this:
, CASE
WHEN TipoFac = 'A' THEN (Amount*Price) END AS 'Total Sale'
, CASE
WHEN TipoFac = 'A' THEN (Amount*Cost) END AS 'Total Cost'
, CASE
WHEN TipoFac = 'A' THEN (Amount*Price) - (Amount*Cost) END AS 'Total Utility'
CodePudding user response：
Firstly, to repeat a lot of my comments.
Don't use literal strings for aliases; this is a bad habit and it doesn't work as people think. Take the query SELECT [name] AS 'DatabaseName' FROM sys.databases ORDER BY 'DatabaseName'; This does not ORDER BY the column aliased as 'DatabaseName', instead it orders by the literal varchar value 'DatabaseName'; thus is has no order, as the literal value is the same for every row. Ideally, don't use object and aliases that require delimit identifying. If you must then either use T-SQL's delimit identify, brackets ([]), or the ANSI-SQL delimit identifier, double quotes (").
Next, you can't reference a column by it's alias in the same query scope apart from in the ORDER BY; SELECT 1 AS A, A 1 AS B; will give an invalid column error, as the column A isn't defined. You would need to repeat the expression again to use that expression in the same scope. If you need to reference a column by its alias, you need to use a derived table or a CTE:
--Derived table
SELECT A,
A 1
FROM (SELECT 1 AS A) DT;
--CTE
WITH CTE AS (
SELECT 1 AS A)
SELECT A,
A 1 AS B
FROM CTE;
Finally, DateE BETWEEN '05/07/2022' AND '05/07/2022 23:59:59' is bad for a couple of reasons; it misses off the last second of the day, and its an ambiguous date format. Use >= and < logic and an unambiguous format; for T-SQL that is yyyyMMdd and yyyy-MM-ddThh:mm:ss(.nnnnnnn). So instead you have DateE >= '20220705' AND DateE < '20220706'.
Now, onto your problem. As I mentioned, there are a couple of options open to use. Personally, I would suggest just repeating the expressions here, as they aren't complicated:
SELECT NumeroD AS Bill, --Don't use literal strings for aliases
Descrip1 AS ProductDescription, --Don't use object names/aliases that require delimit
MAX(ExistAnt) AS [Ex. Act.], --If you must, then use brackets ([]) or double quotes (")
MIN(ExistAnt) AS [Ex. Act.], --but, ideally use camelCase, PascalCase, or snake_case instead (I prefer PascalCase)
--The above 2 columns have the same alias; that can't be right
SUM(CASE WHEN TipoFac = 'A' THEN Amount
WHEN TipoFac = 'B' THEN -Amount
END) AS TotalAmount,
CASE WHEN TipoFac = 'A' THEN (Amount*Price) END AS TotalSale,
CASE WHEN TipoFac = 'A' THEN (Amount*Cost) END AS TotalCost,
CASE WHEN TipoFac = 'A' THEN (Amount*Price) - (Amount*Cost) END AS TotalUtility, --Repeat the expression
CASE WHEN TipoFac = 'A' THEN (((Amount*Price) - (Amount*Cost)) * 100) / (Amount*Price) END AS PercentageUtil,
NroLineaC,
DEsComp
FROM dbo.SAITEMFAC --Always schema qualify your objects
WHERE DateE >= '20220705' AND DateE < '20220706' --Use better date boundaries, not BETWEEN
AND codubic = '0010-01'
AND CodItem = '11001'
GROUP BY Coditem,
NumeroD,
Descrip1,
TipoFac,
Cantidad,
Precio,
Costo;
If you "had to" reference them by their alias, or for more complicated expressions, I would use a CTE. This isn't needed for what you have here, but I'll show you anyway. As you have a nested reference, you'll actually need to use two CTEs:
WITH SalesCost AS(
SELECT NumeroD AS Bill,
Descrip1 AS ProductDescription,
MAX(ExistAnt) AS ExAct1,
MIN(ExistAnt) AS ExAct2, --These cannot have the same name in a CTE/Derived table
SUM(CASE WHEN TipoFac = 'A' THEN Amount
WHEN TipoFac = 'B' THEN -Amount
END) AS TotalAmount,
CASE WHEN TipoFac = 'A' THEN (Amount*Price) END AS TotalSale,
CASE WHEN TipoFac = 'A' THEN (Amount*Cost) END AS TotalCost,
NroLineaC,
DEsComp
FROM dbo.SAITEMFAC --Always schema qualify your objects
WHERE DateE >= '20220705' AND DateE < '20220706' --Use better date boundaries, not BETWEEN
AND codubic = '0010-01'
AND CodItem = '11001'
GROUP BY Coditem,
NumeroD,
Descrip1,
TipoFac,
Cantidad,
Precio,
Costo),
Utility AS(
SELECT Bill,
ProductDescription,
ExAct1,
ExAct2,
TotalAmount,
TotalSale,
TotalCost,
TotalSale - TotalCost AS TotalUtility, --No need for the CASE here, as it's already handled
NroLineaC,
DEsComp
FROM SalesCost)
SELECT Bill,
ProductDescription,
ExAct1,
ExAct2,
TotalAmount,
TotalSale,
TotalCost,
TotalUtility,
(TotalUtility * 100) / TotalSales AS PercentageUtility,
NroLineaC,
DEsComp
FROM Utility;