Creating a SQL query that performs math with varia

问题:

Here's an example of what I've attempted thus far:

A mockup of what the tables look like:

Inventory

ID  |  lowrange  |  highrange
-------------------------------
1   |  15        |  20
2   |  21        |  30

Audit (not used in this query asides from the join)

MissingOrVoid

ID  |  Item  |  Missing  |  Void
---------------------------------
1   |  17    |   1       |  0
1   |  19    |   1       |  0

The most recent query I've attempted to use:

SELECT    I.*,
          SUM(
              (I.HIGHRANGE - I.LOWRANGE + 1)
              - (Count(M.Missing) from M where M.ID = I.ID)
              - (Count(M.Void) from M where M.ID = I.ID)) AS Item_Quantity


FROM Inventory I 
    JOIN Audit A 
        ON A.ID = I.ID 
    JOIN MissingOrVoid M 
        ON M.ID = I.ID

The result should be:

ID  |  lowrange  |  highrange  |  Item_Quantity
-----------------------------------------------
1   |  15        |  20         |  4
2   |  21        |  30         |  10

I can't remember exactly where I've made changes, but in a previous attempt the error message received prior was "Cannot perform an aggregate function on an expression containing an aggregate or a subquery." Currently the error is incorrect syntax near "from" (the one beside M.Missing but in my minimal knowledge of SQL, it appears that these syntax issues cause an outright failure and there may be underlying issues with the query that aren't visible until all of the syntax problems are fixed).

The part where I'm really bombing on is obviously the SUM() section. I am far from a database architect so could someone explain how to correctly perform this and possibly point me in the direction of a resource to learn about this type of function?

Thanks

回答1:

You almost had it right. I am guessing missing/void are BIT types, which you cannot SUM directly.

SELECT I.*,
    (I.HIGHRANGE - I.LOWRANGE + 1)
    - (select Count(nullif(M.Missing,0)) from MissingOrVoid M where M.ID = I.ID)
    - (select Count(nullif(M.Void,0)) from MissingOrVoid M where M.ID = I.ID)
    AS Item_Quantity
FROM Inventory I 

If an item cannot both be missing and void, then

SELECT I.*,
       I.HIGHRANGE - I.LOWRANGE + 1
           - (select Count(case when M.Missing=1 or M.Void=1 then 1 end)
              from MissingOrVoid M where M.ID = I.ID)
           AS Item_Quantity
FROM Inventory I

In fact, if it is only present in MissingOrVoid when it is missing or void, then the CASE in the above query will always be true, so this simplifies to

SELECT I.*,
       I.HIGHRANGE - I.LOWRANGE + 1
           - (select Count(*) from MissingOrVoid M where M.ID = I.ID)
           AS Item_Quantity
FROM Inventory I

回答2:

Initially I have a question as to whether you need to sum those values. If your inventory table has one row per item, that shouldn't be necessary. I'll assume that your table can have multiple rows for a given item, though, and proceed from there.

I think the issue is just a problem with the construction of the subquery. I haven't tested this, but I think it should look more like:

select I.ID, 
    I.Item, 
    SUM(I.HighRange - I.LowRange + 1)
    - (
        select SUM(M.Missing + M.Void)
        from dbo.Audit A
        where A.ID = I.ID
      )
from Inventory I
group by I.ID, I.Item

回答3:

Is this what you're trying to do? I'm not sure what the numbers in the missing and void columns are unless they're just flags...

SELECT    I.*,
          ((I.highrange - I.lowrange + 1)
              - SUM(M.Missing)
              - SUM(M.Void)) AS Item_Quantity


FROM Inventory I 
    JOIN MissingOrVoid M 
        ON M.ID = I.ID

回答4:

The following query works. This assumes there is only one highrange and lowrange for each id.

CREATE TABLE #Inventory (ID INT,Lowrange INT,highrange INT)
CREATE TABLE #MissingOrVoid (Id INT,item INT, missing INT, void INT)

INSERT #Inventory
        ( ID, Lowrange, highrange )
VALUES  ( 1, -- ID - int
          15, -- Lowrange - int
          20  -- highrange - int
          )
INSERT #Inventory
        ( ID, Lowrange, highrange )
VALUES  ( 2, -- ID - int
          21, -- Lowrange - int
          30  -- highrange - int
          )
INSERT #MissingOrVoid
        ( Id, item, missing, void )
VALUES  ( 1, -- Id - int
          17, -- item - int
          1, -- missing - int
          0  -- void - int
          )
INSERT #MissingOrVoid
        ( Id, item, missing, void )
VALUES  ( 1, -- Id - int
          19, -- item - int
          1, -- missing - int
          0  -- void - int
          )


SELECT #Inventory.ID,
       #Inventory.highrange,
       #Inventory.Lowrange,
       highrange-Lowrange+1
           -SUM(ISNULL(missing,0))
           -SUM(ISNULL(void,0)) AS ITEM_QUANTITY
FROM #Inventory
left JOIN  #MissingOrVoid ON #Inventory.ID = #MissingOrVoid.Id
GROUP BY #Inventory.ID,#Inventory.highrange,#Inventory.Lowrange


DROP TABLE #Inventory
DROP TABLE #MissingOrVoid

回答5:

I'd say this would work :

SELECT  I.ID,I.Lowrange as Lowrange,
        I.highrange as Highrange,
        Highrange-Lowrange+1-COUNT(J.missing)-COUNT(J.void) AS ITEM_QUANTITY 
FROM Inventory I
left JOIN  ( select missing as missing, void as void, id from MissingOrVoid 
            ) J
    ON I.ID = J.Id
JOIN Audit A 
    ON A.ID = I.ID          
GROUP BY I.ID,Highrange,Lowrange

But it looks like what RemoteSojourner suggested a lot (and his one is also more esthetic).

回答6:

I'm going to give the derived table approach as it could be faster than a correlated subquery (which run row by row)

SELECT I.*,        
I.HIGHRANGE - I.LOWRANGE + 1 -  MissingVoidCount AS Item_Quantity 
FROM Inventory I  
JOIN 
    (SELECT ID,Count(*) AS MissingVoidCount FROM MissingOrVoid GROUP BY ID) M
        on M.ID = I.ID

Of course in real life, I would never use select *. You also could use a CTE approach.

    ;WITH  MissingVoid(ID, MissingVoidCount) AS
    (
        SELECT ID, Count(*)  FROM MissingOrVoid GROUP BY ID
    )
    SELECT 
        I.*,        
        I.HIGHRANGE - I.LOWRANGE + 1 -  MissingVoidCount AS Item_Quantity 
    FROM Inventory I
    JOIN MissingVoid M
        on M.ID = I.ID