0

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?

4
  • single quotes makes from all columns names strings use double quotes or [] instead, when using it to use it in mathimatical functions Commented Jul 8, 2022 at 17:42
  • Firstly, don't use single quotes (') for aliases. Single quotes are for literal strings, not delimit identifying object names. They only work when you define them, no where else; ORDER BY 'value' would not order by a column aliased as 'value', it would order by the varchar literal 'value' (so would effectively not order at all). Also some syntaxes with literal string aliases are deprecated. Stick to object and alias names that don't need delimit identifying, and if you must delimit identify them use the T-SQL identifier, brackets ([]), or ANSI-SQL's, double quotes ("). Commented Jul 8, 2022 at 17:43
  • Next, you can't reference a column by its alias in the same scope; you would either need to use a derived table, a CTE, or repeat the expression. SELECT 1 AS A, A + 1 AS B; will return the error Invalid column name 'A'., not 1 and 2. SELECT A, A + 1 FROM (SELECT 1 AS A) DT; would work fine though. Commented Jul 8, 2022 at 17:44
  • Lastly, don't use logic like DateE BETWEEN '05/07/2022' AND '05/07/2022 23:59:59'; in this case it'll exclude any rows after 2022-07-05T23:59:59.0000000 and before 2022-07-06T00:00:00.0000000; so almost an entire second of time. Use >= and < logic for explicit boundaries, not BETWEEN, and don't use ambiguous date formats in your literal strings: DateE >= '20220705' AND DateE < '20220706' Commented Jul 8, 2022 at 17:50

2 Answers 2

2

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;
Sign up to request clarification or add additional context in comments.

Comments

0

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'

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.