Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Aggregate function checking 'if contains'

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?

like image 663
m.edmondson Avatar asked Mar 25 '11 17:03

m.edmondson


People also ask

How do you use aggregate function in condition?

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.

Can we use aggregate function in HAVING clause?

The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions.

Is String_agg an aggregate function?

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.


1 Answers

select code,
max(case when page = '999' then 1 else 0 end) as has999
from table
group by code
like image 116
Beth Avatar answered Oct 16 '22 20:10

Beth