asked
Avoiding aggregation when returning at least values
I have a query that looks like this: SELECT a.value, COUNT(*) AS test FROM table1 c inner JOIN table2 a ON c.value2 = a.value_2 inner JOIN table3 o ON c.value3 = o.value_3 AND o.value4 = any int WHERE c.string = 'Some string' GROUP BY a.value HAVING COUNT(*) > 1 This is selecting a value from table 2 when 'Some string' occurs more than once. Does anyone know a way of expressing the same query without using aggregation? Apologies for the lack of formatting new to this forum. I am using PostgreSQL also
I hope I understand the question correctly. I am not sure about the purpose of it, but if the query in question needs to be restated so it does not use the formal **group by** then it can be done in PostgreSQL in exactly same way as it would be done in T-SQL (there are no syntax differences for something like that, PostgreSQL fully supports windowing functions, just like SQL Server does). By the way, so long as the count(all) is used in the select list, there is still some aggregation taking place regardless of whether the ** group by** clause is used or not. Here is one way of doing it: select * from ( select a.[value], count(*) over (partition by a.[value]) as test from table1 cinner join table2 a on c.value2 = a.value_2 inner join table3 o on c.value3 = o.value_3 and o.value4 = any int where c.string = 'some string' ) t where test > 1; The only advantage of using this method is that it is possible to include any other columns from any of the participating tables in the select list because there is no group by clause. Hope this helps. Oleg
18 People are following this question.
