modify a query to show zero value-CodePudding

I usually use the below query:

select 
    item, sum(qty), area,
from 
    my_table 
group by
    area, item

the issue I have is if the qty is zero I don't see the area or the items while I am looking to see all areas even when the item is ZERO value

looking for your kind help

CodePudding user response：

welcome to Stack Overflow.

First let's have a chat about how to provide demo data when asking questions like these. Ideally you'd give us enough info to be able to reproduce an example set and object definitions.

Consider:

DECLARE @my_table TABLE (Item NVARCHAR(30), Qty SMALLINT, Area NVARCHAR(30));

INSERT INTO @my_table (item, Qty, Area) VALUES
('Product One', 0, 'Area One'),('Product One', NULL, 'Area Two'),('Product One', 2, 'Area Three'),
('Product Two', 0, 'Area One'),('Product Two', NULL, 'Area Two'),('Product Two', 2, 'Area Three'),
('Product Three', 0, 'Area Three'),('Product Three', NULL, 'Area Two'),('Product Three', 2, 'Area Three'),
('Product One', 0, 'Area One'),('Product One', NULL, 'Area Two'),('Product One', 2, 'Area Three'),
('Product Two', 0, 'Area One'),('Product Two', NULL, 'Area Two'),('Product Two', 2, 'Area Three'),
('Product Three', 0, 'Area Three'),('Product Three', NULL, 'Area Two'),('Product Three', 2, 'Area Three'),
('Product One', 0, 'Area One'),('Product One', NULL, 'Area Two'),('Product One', 2, 'Area Three'),
('Product Two', 0, 'Area One'),('Product Two', NULL, 'Area Two'),('Product Two', 2, 'Area Three'),
('Product Three', 0, 'Area Three'),('Product Three', NULL, 'Area Two'),('Product Three', 2, 'Area Three');

This defines a table, with appropriate data types and insets some data into it. Anyone can easily run the snippet and use the resulting table variable.

Now on to your question. You're using the SUM aggregate function which adds the values together. The problem you're running in to, I think, is that when you operate on a NULL the result is a NULL. 1 2 NULL = NULL.

To avoid this we can use the COALESCE function. It accepts a list of values, and returns the first non NULL one:

COALESCE(NULL,NULL,NULL,1) will return 1 COALESCE(NULL,2,1) will return 2, because it was encountered first.

SELECT my.item, SUM(COALESCE(my.qty,0)) AS TotalQty, my.area
  FROM @my_table my
GROUP BY my.area, my.item;

This has 0 substituted in for any NULLs which can be summed.

item            TotalQty    area
--------------------------------
Product One     0           Area One
Product One     6           Area Three
Product One     0           Area Two
Product Three   6           Area Three
Product Three   0           Area Two
Product Two     0           Area One
Product Two     6           Area Three
Product Two     0           Area Two

All the Qty values are now summed.

This might not be the problem you are having though, without the data it can be hard to tell.

If the problem you're trying to solve is displaying a SUM for combinations that don't exist in your data set the solution will be different.

If we add a new row to @my_table:

DECLARE @my_table TABLE (Item NVARCHAR(30), Qty SMALLINT, Area NVARCHAR(30));

INSERT INTO @my_table (item, Qty, Area) VALUES
('Product Four', 0, 'Area Three');

We now have a product in the table with only a row for Area Three. This means there won't be an aggregate row for Area Two or Area one.

To do that we need to provide a list of all the combinations we think are possible. You can do this in which ever method you'd like, but without further data and objects it's hard to know which is the best way.

Using just the data we have:

;WITH Items AS (
SELECT DISTINCT mt.item
  FROM @my_table mt
), Areas AS ( 
SELECT DISTINCT mt.area
  FROM @my_table mt
), AllItemsAndAreas AS (
SELECT *
  FROM Items i
    CROSS APPLY Areas a
)

SELECT aiaa.item, SUM(COALESCE(qty,0)) AS TotalQty, aiaa.Area
  FROM AllItemsAndAreas aiaa
    LEFT OUTER JOIN @my_table mt
      ON aiaa.item = mt.Item
      AND aiaa.area = mt.Area
 GROUP BY aiaa.item, aiaa.area;

There's a few things going on here. First, we've defined a CTE (Common Table Expression) which finds a distinct list of items. We did the same for Areas. Finally we defined a CTE that performs a cross apply, making a row for each item and area combination in the table.

Then we selected from that CTE and LEFT OUTER joined it to the actual table. Even when there are no matches, the join keeps the row and populates the unmatched columns with NULL.

As before, we perform your aggregate, using the COALESCE to replace the NULLs. Now we have rows for all the possible combinations in our result set, even when they don't actually exist.

item            TotalQty    Area
---------------------------------
Product Four    0           Area One
Product One     0           Area One
Product Three   0           Area One
Product Two     0           Area One
Product Four    0           Area Three
Product One     6           Area Three
Product Three   6           Area Three
Product Two     6           Area Three
Product Four    0           Area Two
Product One     0           Area Two
Product Three   0           Area Two
Product Two     0           Area Two

I'd also like to tell you about aliases. We can give most objects in a SQL query an alias. This is useful when you reference multiple tables within a single query, or for columns returned on an operation. You can see in the example query I used an alias for the SUM() as TotalQty. This gives the column a name in the result set. I also used an alias for the table and could then refer to it's columns as my.item. While note strictly necessary for a trivial query like this, it's a good habit to get into.