0

I am working on implementation for a SQL which should display results with Union operation between Where and Having Clause. For example, 

Select * from table where col1= 'get' group by col2 (OR/UNION) having avg(col3) >30 . This is not valid but trying to give use a case

The purpose of the sql statement is to return result set which satisfies both where and having conditions.

Lets say I have a table1, has with col1, col2, col3, col4 and large data in the table. Now, There is a use case in which user wants to see results when selects filters with specific crtieria col1 ='Y', avg(col2) >10, avg(col3*col4) =30 in filters list. Now, I have to create a criteria, such that, I should return all results which satisfies col1 ='Y' OR avg(col2) >10 OR avg(col3*col4) =30 , like we do in where clause with OR operator but here we have both where clause and having clause –

Like, the below query

resultset1 <= select * from table1 where col1= 'get'; resultset2 <= select * from table1 group by col2 having avg(col3) >30

final results = resultset1+ resultset2 Do any one have better approach or ideas in implementing such scenario?

Lets say I have filters combinations as below

col1 =23 OR avg(col2) >30 AND avg(col3) =10 OR avg(col1) <10 AND col2 =10

I need to display results satisfying these criteria in SQL

TechJump
  • 79
  • 1
  • 12
  • 3
    This query is invalid in most RDBMS because you are doing `SELECT *` with a `GROUP BY` query. Please update your question with sample data and expected output. – Tim Biegeleisen Dec 14 '16 at 05:33
  • Show us the table structure, sample data and expected output to let us help you better. – Sateesh Pagolu Dec 14 '16 at 05:35
  • Question says *where and having* but query says *where or having* so which one is correct – Pரதீப் Dec 14 '16 at 05:41
  • It's not clear what you want to achieve with this SQL. Just describe a problem you try to solve. – valex Dec 14 '16 at 06:28
  • @TimBiegeleisen , I know the query is invalid but I am trying to make a case to explain the problem. – TechJump Dec 14 '16 at 06:46
  • I have a table1, has with col1, col2, col3, col4 and large data in the table. Now, There is a use case in which user wants to see results when selects filters with specific crtieria col1 ='Y', avg(col2) >10, avg(col3*col4) =30 in filters list. Now, I have to create a criteria, such that, I should return all results which satisfies col1 ='Y' OR avg(col2) >10 OR avg(col3*col4) =30 , like we do in where clause with OR operator but here we have both where clause and having clause – TechJump Dec 14 '16 at 06:53
  • Even after your edits, you still seem to be confused about how `GROUP BY` works. Please show clear input and expected output, as you have not done this. – Tim Biegeleisen Dec 14 '16 at 07:01
  • @TimBiegeleisen in general I understand the criteria as they are not mutually exclusive. But I want to treat them as mutually exclusive and return combine list. GROUP BY if needed can be ignored. But I want to combine where clause and having clause results like OR not like AND condition – TechJump Dec 14 '16 at 07:03
  • @Prdp OR operation between having & where clauses - – TechJump Dec 14 '16 at 07:07

2 Answers2

0

It's not clear what do you want from this quasi SQL. I guess you need to select records with two conditions col1= 'get' AND /OR ? having avg(col3) >30. So here is the solution:

Select * from table 
 where (col1= 'get')
 OR 
 col2 in (SELECT col2 FROM table GROUP BY col2 HAVING avg(col3) >30)

If you need both conditions where true then replace OR with AND.

If you need to count AVG only for col1 = 'get' then add this condition into the subquery:

Select * from table 
 where (col1= 'get')
 OR 
 col2 in (SELECT col2 FROM table WHERE (col1= 'get') 
                      GROUP BY col2 
                      HAVING avg(col3) >30)
valex
  • 23,966
  • 7
  • 43
  • 60
  • In this case, Having avg(col3) >30 is applied on top filtered list based on col1 = 'get' but I need results which displays both avg(col3) >30 and col1='get' . where one uses where-clause and other uses having clause. – TechJump Dec 14 '16 at 06:57
  • @TechJump the first query from my answer with `OR` actually do this. – valex Dec 14 '16 at 07:04
  • Thanks valex let me try it and I am also concerned about the performance as there is huge data, having multiple like conditions might impact performance? – TechJump Dec 14 '16 at 07:12
  • Lets say I have filters combinations as below col1 =23 OR avg(col2) >30 AND avg(col3) =10 OR avg(col1) <10 AND col2 =10 I need to display results satisfying these criteria in SQL – TechJump Dec 14 '16 at 07:15
0

SELECT <resultset1> --resultset based on a WHERE clause UNION SELECT <resultset2> --resultset based on HAVING

In general, if you want a union of resultsets, use ... UNION.

Using OR in a condition is equivalent to UNION (because the UNION operator is the relational algebra equivalent of logical disjunction), but it requires the scope of the involved conditions to be identical.

In this case, this is impossible because a HAVING condition applies not to the table mentioned in the SELECT, but instead to an intermediate table that is "silently" created by the GROUP clause. This is inevitably so because things like AVG,SUM,... only make sense if it is also determined which set of rows must be used to compute the AVG,SUM,... over, and that is what the GROUP BY specification does.

EDIT

In SQL, UNION comes in distinct flavours, UNION DISTINCT and UNION ALL. One eliminates duplicates, the other won't. If you want the exact same behaviour as OR, you'll obviously need the one that eliminates duplicates from its result set.

Erwin Smout
  • 18,113
  • 4
  • 33
  • 52
  • If I follow this approach, what is the impact on performance? – TechJump Dec 14 '16 at 17:01
  • Performance is a function of (a) the nature of the filtering predicates (b) the nature of the physical design of the db (which indexes etc.) and (c) the specific features built into the optimizer of the specific version of the specific DBMS you are using. And most of the times also (d) which specific statistics are being collected about the db. Do you seriously expect anything like a firm "none" as an answer to this unanswerable question ? – Erwin Smout Dec 16 '16 at 07:56