Custom aggregation in GROUP BY clause
If I have a table with a schema like this
table(Category, SubCategory1, SubCategory2, Status)
I would like to group by Category, SubCategory1 and aggregate the Status such that if not all Status values over the group have a certain value Status will be 0 otherwise 1.
So my result set will look like
(Category, SubCategory1, Status)
I don't want to 开发者_运维百科write a function. I would like to do it inside the query.
Assuming that status
is a numeric data type, use:
SELECT t.category,
t.subcategory1,
CASE WHEN MIN(t.status) = MAX(t.status) THEN 1 ELSE 0 END AS status
FROM dbo.TABLE_1 t
GROUP BY t.category, t.subcategory1
You can test that both the minimum and maximum status for each group are equal to your desired value:
SELECT
category,
subcategory1,
CASE WHEN MIN(status) = 42 AND MAX(status) = 42 THEN 1 ELSE 0 END AS Status
FROM table1
GROUP BY category, subcategory1
Let's say you want to find groups that have all status values under 100
SELECT category, subcategory1,
CASE WHEN MAX(status) < 100 THEN 0 ELSE 1 END AS Status
FROM table1
GROUP BY category, subcategory1
All groups with status under 100 will have Status set to 0, and all groups with at least one status >= 100 will be set to 1.
I think that's what you're asking for, but if not let me know.
I would like to group by Category, SubCategory1 and aggregate the Status such that if not all Status values over the group have a certain value Status will be 0 otherwise 1.
I'm interpreting this as "If there exists a Status value in a given group not equal to a given parameter, the returned Status will be 0 otherwise 1".
Select T.Category, T.SubCategory1
, Case
When Exists(
Select 1
From Table As T2
Where T2.Category = T.Category
And T2.SubCategory1 = T.SubCategory1
And T2.Status <> @Param
) Then 0
Else 1
End As Status
From Table As T
Group By t.Category, T.SubCategory1
Something like that :
select
Category,
SubCategory1,
(
case
when good_record_count = all_record_count then 1
else 0
end
) as all_records_good
from (
select
t.Category,
t.SubCategory1,
sum( cast(coalesce(t.Status, 'GOOD', '1', '0') as int) ) good_record_count,
count(1) all_record_count
from
table_name t
group by
t.Category, t.SubCategory1
)
精彩评论