Vertica - Filter results using HAVING only on some columns

Is it possible to use the HAVING clause in Vertica only on some of my columns? Looking for the most efficient way as I am dealing with millions of rows.

Here's an example of what I am trying to do: Given the table t:

A | B | number --+---+------- a | c | 1 a | c | 2 a | d | 1 b | e | 1 b | e | 1 c | c | 1 

If I run:

SELECT A, B, sum(number) as total FROM t GROUP BY 1, 2 HAVING sum(number) >= 2 

I will get:

A | B | total --+---+------ a | c | 3 b | e | 2 

I would like to somehow be able to apply the HAVING condition just to column A, and not to the (A, B) combination, so that my result will really be:

A | B | total --+---+------ a | c | 3 a | d | 1 b | e | 2 

Guess I could be doing it like this:

SELECT A, B, sum(number) as total FROM t WHERE A IN (     SELECT A     FROM t     GROUP BY 1     HAVING sum(number) >= 2 ) GROUP BY 1, 2 

But is there a better way?

Replay

Category: sql Time: 2016-07-29 Views: 0

Related post

iOS development

Android development

Python development

JAVA development

Development language

PHP development

Ruby development

search

Front-end development

Database

development tools

Open Platform

Javascript development

.NET development

cloud computing

server

Copyright (C) avrocks.com, All Rights Reserved.

processed in 0.216 (s). 12 q(s)