Group by issue in sql server

Question

Hi all I have written a query to display the sum of quantity as follows without group by

SELECT ISNULL(SUM(VUItems.Quantity), 0) AS OrderQty
FROM   VUItems

This returns as 0.00 but the same query when using group by not displaying 0.00 what might be the problem

SELECT ISNULL(SUM(VUItems.Quantity), 0) AS OrderQty
FROM   VUItems
GROUP  BY SKU,
          SalesOrderNo

Why I need is that I will have a table which will have the quantity on saving this is what I have written to display the Quantity ordered or user eneterd as follows

SELECT VU1.*,
       VU1.Quantity - (SELECT ISNULL(SUM(VU2.Quantity), 0) AS OrderQty
                       FROM   VU2
                       WHERE  VU1.SKU = VU2.SKU
                              AND VU1.SalesOrderNo = VU2.SalesOrderNo
                       GROUP  BY SKU) AS orderedQuantity
FROM   VU1

with out group by it is displaying orderedQuantity as required but with group by it is showing null

By design: http://stackoverflow.com/questions/2552086/does-count-always-return-a-result/2552102#2552102 (and duplicate) — gbn, May 21 '13 at 14:47
Assuming `VUItems` is an empty table then this is just the difference between [scalar and vector](http://sqlblog.com/blogs/paul_white/archive/2012/03/12/fun-with-aggregates.aspx) aggregates. — Martin Smith, May 21 '13 at 14:50
Hi `gbn` even I used `count` when I group by a column name it is not displaying `null` without group by it is displaying `null` — Learner, May 21 '13 at 15:05
@Learner: count, sum. min, max all behave the same. See my answer on my link above — gbn, May 21 '13 at 15:10
Following your edit I don't see the problem. `GROUP BY SKU` is unnecessary anyway as the `WHERE` clause ensures that at most one `SKU` will match so just leave it out. — Martin Smith, May 21 '13 at 15:22

score 0 · Answer 1 · answered May 21 '13 at 18:09

0

The ISNULL needs to be inside the SUM. SUM(NULL) is NULL.

answered May 21 '13 at 18:09

Pirion

519
2
6

Group by issue in sql server

1 Answers1