February 12, 2018 at 11:13 am
I've got a parameter table that I need to get counts from. The table contains a multidimensional set of ID's/Values. . I know I can accomplish this in code but struggling with how to handle this in a single statement. The query also needs to be dynamic as the conditions will change.
Any help is appreciated!
Based on the example below, I'm trying to get a count of the following:
AttributeID = 5022 and Value <=7 and (AttributeID = 5023 and has a Value of 'TRUE' or AttributeID = 5024 and has a Value of 'TRUE' )
Expected result: 3
CREATE TABLE #mytable
( ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
, Param int
, AttributeID int
, Value Varchar(10)
)
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #mytable ON
--===== Insert the test data into the test table
INSERT INTO #mytable
(ID, Param, AttributeID, Value)
select '133915', '5021', '1' UNION ALL
select '133915', '5022', '5' UNION ALL
select '133915', '5023', 'TRUE' UNION ALL
select '135122', '5022', '5' UNION ALL
select '135287', '5007', '2' UNION ALL
select '135287', '5009', '2' UNION ALL
select '135287', '5021', '2' UNION ALL
select '135287', '5022', '2' UNION ALL
select '135287', '5024', 'TRUE' UNION ALL
select '135360', '5031', '5' UNION ALL
select '135361', '5031', '5' UNION ALL
select '135362', '5024', 'TRUE' UNION ALL
select '135362', '5022', '8' UNION ALL
select '135363', '5023', 'TRUE' UNION ALL
select '135363', '5022', '7'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #mytable ON
February 12, 2018 at 11:20 am
thinkink07 - Monday, February 12, 2018 11:13 AMI've got a parameter table that I need to get counts from. The table contains a multidimensional set of ID's/Values. Based on the example below, I'm trying to get a count of the following: AttributeID = 5022 and Value <=7 and AttributeID 5023 or 5024 and has a Value of 'TRUE'. I know I can accomplish this in code but struggling with how to handle this in a single statement. The query also needs to be dynamic as the conditions will change.Any help is appreciated!
Expected result: 3
Param AttributeID Value 133915 5021 1 133915 5022 5 133915 5023 TRUE 135122 5022 5 135287 5007 2 135287 5009 2 135287 5021 2 135287 5022 2 135287 5024 TRUE 135360 5031 5 135361 5031 5 135362 5024 TRUE 135362 5022 8 135363 5023 TRUE 135363 5022 7
Would you please provide your data in a format which can be pasted into SSMS and executed (see the link in my signature for details)? Also, note that the condition you mention (AttributeID = 5022 and Value <=7 and AttributeID 5023 or 5024 and has a Value of 'TRUE') will never return any results (because an attributeId cannot simultaneously be 5022 and 5023). Did you get your ANDs and ORs mixed up?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 12, 2018 at 11:50 am
thinkink07 - Monday, February 12, 2018 11:13 AM
Would you please provide your data in a format which can be pasted into SSMS and executed (see the link in my signature for details)? Also, note that the condition you mention (AttributeID = 5022 and Value <=7 and AttributeID 5023 or 5024 and has a Value of 'TRUE') will never return any results (because an attributeId cannot simultaneously be 5022 and 5023). Did you get your ANDs and ORs mixed up?
Thx for the tip! Updated the topic...
February 12, 2018 at 11:51 am
This looks like an EAV table. Storing things in an EAV table makes it much harder to query. You can use Cross Tabs and Pivots to change your EAV table into a normal table, which will make your queries easier.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 12, 2018 at 12:11 pm
Your requirements decode directly into SQL with very few changes:SELECT *
FROM #mytable m
WHERE
m.AttributeID = 5022
AND m.Value <= 7
AND
(
(
m.AttributeID = 5023
AND m.Value = 'TRUE'
)
OR
(
m.AttributeID = 5024
AND m.Value = 'TRUE'
)
);
However, as I mentioned earlier, this will never select any rows. Please rethink your selection logic.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 12, 2018 at 12:18 pm
Your code was helpful, but not runnable (you set IDENTITY_INSERT ON but then did not include the IDENTITY column in your INSERT statement). Here is a version which runs OK:
DROP TABLE IF EXISTS #mytable;
CREATE TABLE #mytable
( ID INT PRIMARY KEY CLUSTERED IDENTITY(1,1)
, Param int
, AttributeID int
, Value Varchar(10)
)
INSERT #mytable
(Param, AttributeID, Value)
values ('133915', '5021', '1') ,
('133915', '5022', '5' ),
('133915', '5023', 'TRUE' ),
('135122', '5022', '5' ),
('135287', '5007', '2' ),
('135287', '5009', '2' ),
('135287', '5021', '2' ),
('135287', '5022', '2' ),
('135287', '5024', 'TRUE' ),
('135360', '5031', '5' ),
('135361', '5031', '5' ),
('135362', '5024', 'TRUE' ),
('135362', '5022', '8' ),
('135363', '5023', 'TRUE' ),
('135363', '5022', '7' )
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 12, 2018 at 12:24 pm
Instead of doing a crosstab/pivot, you can also just use a HAVING clause with the correct criteria.
SELECT [Param]
FROM #mytable
GROUP BY [Param]
HAVING MAX(CASE WHEN AttributeID = 5022 AND TRY_CAST(Value AS INT) <= 7 THEN 1 END) = 1
AND MAX(CASE WHEN AttributeID IN ('5023', '5024') AND Value = 'TRUE' THEN 1 END) = 1
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 12, 2018 at 1:10 pm
Phil Parkin - Monday, February 12, 2018 12:11 PMYour requirements decode directly into SQL with very few changes:SELECT *
FROM #mytable m
WHERE
m.AttributeID = 5022
AND m.Value <= 7
AND
(
(
m.AttributeID = 5023
AND m.Value = 'TRUE'
)
OR
(
m.AttributeID = 5024
AND m.Value = 'TRUE'
)
);However, as I mentioned earlier, this will never select any rows. Please rethink your selection logic.
So based on the example code I provided, I'm expecting parms 133915, 135287, 1335363 to meet the criteria. They each have Attribute 5022 with a Value <= 7 and they have either Attribute 5023 or 5024 with a Value of 'true'.
My apologies for the error earlier...
February 12, 2018 at 1:26 pm
thinkink07 - Monday, February 12, 2018 1:10 PMPhil Parkin - Monday, February 12, 2018 12:11 PMYour requirements decode directly into SQL with very few changes:SELECT *
FROM #mytable m
WHERE
m.AttributeID = 5022
AND m.Value <= 7
AND
(
(
m.AttributeID = 5023
AND m.Value = 'TRUE'
)
OR
(
m.AttributeID = 5024
AND m.Value = 'TRUE'
)
);However, as I mentioned earlier, this will never select any rows. Please rethink your selection logic.
So based on the example code I provided, I'm expecting parms 133915, 135287, 1335363 to meet the criteria. They each have Attribute 5022 with a Value <= 7 and they have either Attribute 5023 or 5024 with a Value of 'true'.
My apologies for the error earlier...
The query suggested by Drew above seems to do exactly what you need.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 12, 2018 at 1:27 pm
drew.allen - Monday, February 12, 2018 12:24 PMInstead of doing a crosstab/pivot, you can also just use a HAVING clause with the correct criteria.
SELECT [Param]
FROM #mytable
GROUP BY [Param]
HAVING MAX(CASE WHEN AttributeID = 5022 AND TRY_CAST(Value AS INT) <= 7 THEN 1 END) = 1
AND MAX(CASE WHEN AttributeID IN ('5023', '5024') AND Value = 'TRUE' THEN 1 END) = 1Drew
Thx Drew, this works at providing the Params that should be returned. However, getting a count is not returning what I would expect. I would think count(distinct Param) would work but it returns a list of 1's. What am I missing?
Edit: I can throw it into a derived query and it works. Is there a more elegant way?
February 12, 2018 at 1:39 pm
thinkink07 - Monday, February 12, 2018 1:27 PMdrew.allen - Monday, February 12, 2018 12:24 PMInstead of doing a crosstab/pivot, you can also just use a HAVING clause with the correct criteria.
SELECT [Param]
FROM #mytable
GROUP BY [Param]
HAVING MAX(CASE WHEN AttributeID = 5022 AND TRY_CAST(Value AS INT) <= 7 THEN 1 END) = 1
AND MAX(CASE WHEN AttributeID IN ('5023', '5024') AND Value = 'TRUE' THEN 1 END) = 1Drew
Thx Drew, this works at providing the Params that should be returned. However, getting a count is not returning what I would expect. I would think count(distinct Param) would work but it returns a list of 1's. What am I missing?
Edit: I can throw it into a derived query and it works. Is there a more elegant way?
That's exactly what I would expect. If you do a distinct count on any of the grouping expressions, you're going to get 1, because the grouping expression defines the group, and any change in the grouping function would specify a different group.
Perhaps you are looking for COUNT(AttributeID) or COUNT(DISTINCT AttributeID) rather than COUNT(DISTINCT Param).
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 12, 2018 at 2:24 pm
drew.allen - Monday, February 12, 2018 11:51 AMThis looks like an EAV table. Storing things in an EAV table makes it much harder to query. You can use Cross Tabs and Pivots to change your EAV table into a normal table, which will make your queries easier.Drew
https://www.red-gate.com/simple-talk/sql/t-sql-programming/avoiding-the-eav-of-destruction/
https://www.postgresql.org/message-id/4BE45F66.5070402@magwerks.com
Please post DDL and follow ANSI/ISO standards when asking for help.
February 12, 2018 at 2:33 pm
thinkink07 - Monday, February 12, 2018 1:27 PMdrew.allen - Monday, February 12, 2018 12:24 PMInstead of doing a crosstab/pivot, you can also just use a HAVING clause with the correct criteria.
SELECT [Param]
FROM #mytable
GROUP BY [Param]
HAVING MAX(CASE WHEN AttributeID = 5022 AND TRY_CAST(Value AS INT) <= 7 THEN 1 END) = 1
AND MAX(CASE WHEN AttributeID IN ('5023', '5024') AND Value = 'TRUE' THEN 1 END) = 1Drew
Thx Drew, this works at providing the Params that should be returned. However, getting a count is not returning what I would expect. I would think count(distinct Param) would work but it returns a list of 1's. What am I missing?
Edit: I can throw it into a derived query and it works. Is there a more elegant way?
Maybe?:
SELECT [Param],
SUM(CASE WHEN AttributeID = 5022 AND TRY_CAST(Value AS INT) <= 7 THEN 1 ELSE 0 END) AS Matching_Row_Count
FROM #mytable
GROUP BY [Param]
HAVING MAX(CASE WHEN AttributeID = 5022 AND TRY_CAST(Value AS INT) <= 7 THEN 1 END) = 1
AND MAX(CASE WHEN AttributeID IN ('5023', '5024') AND Value = 'TRUE' THEN 1 END) = 1
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 12, 2018 at 2:47 pm
thinkink07 - Monday, February 12, 2018 1:27 PMdrew.allen - Monday, February 12, 2018 12:24 PMInstead of doing a crosstab/pivot, you can also just use a HAVING clause with the correct criteria.
SELECT [Param]
FROM #mytable
GROUP BY [Param]
HAVING MAX(CASE WHEN AttributeID = 5022 AND TRY_CAST(Value AS INT) <= 7 THEN 1 END) = 1
AND MAX(CASE WHEN AttributeID IN ('5023', '5024') AND Value = 'TRUE' THEN 1 END) = 1Drew
Thx Drew, this works at providing the Params that should be returned. However, getting a count is not returning what I would expect. I would think count(distinct Param) would work but it returns a list of 1's. What am I missing?
Edit: I can throw it into a derived query and it works. Is there a more elegant way?
You haven't said what counts you are expecting, so it's rather difficult to tell you how to get there.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 12, 2018 at 2:58 pm
I'm wanting a count of Param to get the number that meet the criteria. Your example works and I can get the count by wrapping it in a derived query. Just was't sure if there was a better way..
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply