I have data:
Code Page Task
2 1 Activity
2 2 Activity
2 3 Activity Symbols
2 4 Activity Symbols
2 5 Activity Symbols
2 12 Activity
2 12 Activity Symbols
2 999 Consider
2 999 Constituents
2 999 Material
2 999 Material Hazards
316 999 Constituents
356 999 Constituents
398 999 Constituents
604 70 Activity Symbols
604 999 Constituents
I want to group by Code
and for each check if any of the records in that group contain a Page
of '999', if so return 'True'. Is there an aggregate function that will do this? Or perhaps there is a better way to structure a query like this?
An aggregate function can be used in a WHERE clause only if that clause is part of a subquery of a HAVING clause and the column name specified in the expression is a correlated reference to a group. If the expression includes more than one column name, each column name must be a correlated reference to the same group.
The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions.
STRING_AGG is an aggregate function that takes all expressions from rows and concatenates them into a single string. Expression values are implicitly converted to string types and then concatenated.
select code,
max(case when page = '999' then 1 else 0 end) as has999
from table
group by code
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With