April 12, 2012 at 12:40 pm
I have data that when grouped by 4 fields, and using a having clause, displays records that don't appear to meet the having clause criteria, and shows aggregate values that don't appear in the source data. Thus I wonder if I perhaps don't quite understand the HAVING clause ...
Here's the query:
SELECT Key1, Key2, Key3, Key4,
SUM(VOL_CEQ) AS TTL_VOL_CEQ,
SUM(VOL_UNIT) AS TTL_VOL_UNIT,
COUNT(DISTINCT ITEMID) AS ITEMID_COUNT,
MIN(VOL_CEQ) AS MIN_VOL_CEQ,
MAX(VOL_CEQ) AS MAX_VOL_CEQ,
MIN(VOL_UNIT) AS MIN_VOL_UNIT,
MAX(VOL_UNIT) AS MAX_VOL_UNIT
INTO dbo.ZERO_VOLUME_DATA
FROM SOURCE_TABLE
GROUP BY Key1, Key2, Key3, Key4
HAVING COUNT(DISTINCT ITEMID) > 1
AND
(
MAX(VOL_CEQ) <> MIN(VOL_CEQ)
OR
MAX(VOL_UNIT) <> MIN(VOL_UNIT)
)
AND
(
SUM(VOL_CEQ) = 0
OR
SUM(VOL_UNIT) = 0
)
ORDER BY Key1, Key2, Key3, Key4
SELECT ZV.Key1, ZV.Key2, ZV.Key3, ZV.Key4
FROM dbo.ZERO_VOLUME_DATA AS ZV
INNER JOIN SOURCE_TABLE AS ST
ON ZV.Key1 = ST.Key1
AND ZV.Key2 = ST.Key2
AND ZV.Key3 = ST.Key3
AND ZV.Key4 = ST.Key4
The data for one particular value of Key1 shows 1 record where the SUM(VOL_CEQ) and SUM(VOL_UNIT) values are shown as zero, with the MAX and MIN values equal to 1 and -1, respectively, for both VOL_CEQ and VOL_UNIT. However, when you join this query result back to the original source table using all 4 keys, so that you can see the actual source values that contributed to the aggregates, the value for VOL_CEQ and VOL_UNIT for this particular value of Key1 are all equal to 1. No negatives anywhere. Do I not understand how HAVING works, or am I hitting a bug, or ???
Here's the join query that shows the problem:
SELECT ZV.Key1, ZVS.Key2, ZV.Key3, ZV.Key4, ST.ITEMID, ST.VOL_CEQ, ST.VOL_UNIT, ZV.ITEMID_COUNT,
ZV.MIN_VOL_CEQ, ZV.MAX_VOL_CEQ, ZV.MIN_VOL_UNIT, ZV.MAX_VOL_UNIT
FROM dbo.ZERO_VOLUME_DATA AS ZV
INNER JOIN SOURCE_TABLE AS ST
ON ZV.Key1 = ST.POC_STATE
AND ZV.Key2 = ST.Key2
AND ZV.Key3 = ST.Key3
AND ZV.Key4 = ST.Key4
Steve
(aka sgmunson)
:w00t::w00t::w00t:
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 12, 2012 at 1:27 pm
can you post some sample data and table definitions so we can see what you are looking at for an output??
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 12, 2012 at 1:41 pm
Here's the portion of the output that has the problem:
Key1 Key2 Key3 Key4 ITEMID VOL_CEQ VOL_UNIT ITEMID_COUNT MIN_VOL_CEQ MAX_VOL_CEQ MIN_VOL_UNIT MAX_VOL_UNIT
TN K2Val K3Value K4Value Item1 112-11-11
TN K2Val K3Value K4Value Item2 112-11-11
The data itself is too confidential and there's way too much of it to have it be practical to provide sample data, but the two records of output I get from the original source table make it impossible to understand how the -1 value for the two MIN values ever came into play, unless there's something I don't understand about the HAVING clause. I also tried re-writing the query to do the GROUP BY without the having in a WITH clause, then applying the conditions for HAVING in the WHERE clause to the query that follows the WITH clause, and the results were identical.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 12, 2012 at 1:54 pm
The gremlins are everywhere....
Sadly, the problem is fixed because somebody fat-fingered a table name in existing code and after OBFUSCATING it for publication here, I could no longer tell the difference, until I realized it was a VERY SIMILAR table name, and bingo, problem solved. Sorry to trouble everyone.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 12, 2012 at 2:07 pm
sgmunson (4/12/2012)
The gremlins are everywhere....Sadly, the problem is fixed because somebody fat-fingered a table name in existing code and after OBFUSCATING it for publication here, I could no longer tell the difference, until I realized it was a VERY SIMILAR table name, and bingo, problem solved. Sorry to trouble everyone.
no trouble. glad you solved your problem
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply