Why not? How else would you aggregate revenue over several dimensions?
- Total revenue, revenue per business unit, revenue per sales
employee: ROLLUP
- Total revenue, revenue per business unit, revenue per country,
revenue per country and business unit: CUBE
The same can be achieved with lo
2015-10-28 11:26 GMT+01:00 Rami Ojares :
> Ok, now I think I got it.
> GROUP BY () groups all the rows into one group (although logically they
> were one group already)
> but this is needed if one wants to use aggregate operators in the
> restriction of rows.
> And now that all the rows are in a g
Ok, now I think I got it.
GROUP BY () groups all the rows into one group (although logically they
were one group already)
but this is needed if one wants to use aggregate operators in the
restriction of rows.
And now that all the rows are in a group we can use the having clause to
filter that g
2015-10-28 10:59 GMT+01:00 Rami Ojares :
> Not grouped by the title column. The title columns are aggregated via a
> concatenation operation: string_agg()
>
> Does that mean that the select clause is interpreted before the having
> clause?
>
No, what made you think so?
> To be more specific what
Not grouped by the title column. The title columns are aggregated via
a concatenation operation: string_agg()
Does that mean that the select clause is interpreted before the having
clause?
To be more specific what does GROUP BY() mean?
() is the empty GROUPING SET. The subtle diffe
2015-10-27 13:21 GMT+01:00 Rami Ojares :
>
>
> SELECT
> string_agg(title, ', ')
> FROM
> film
> WHERE
> title LIKE 'AN%'
> HAVING
> count(*) > 5
>
>
> "Return a concatenation of all the films starting with "AN", *IF* there
> are at least 5 such films."
>
>
> Where in the statement does it
That's so good I just wanted to see it by itself and repeat it to everyone!
:D
On 27/10/2015 4:28 PM, Lukas Eder wrote:
As far as I'm concerned, nothing beats a good glass of red wine whilst
reading the SQL standard documents in front of the fireplace. Somehow,
few people are with me on that ;
SELECT
string_agg(title, ', ')
FROM
film
WHERE
title LIKE 'AN%'
HAVING
count(*) > 5
"Return a concatenation of all the films starting with "AN", *IF*
there are at least 5 such films."
Where in the statement does it say that the rows should be grouped b
2015-10-27 8:51 GMT+01:00 Rami Ojares :
> Thanks Lukas!
> It is always a pleasure to get these nuggets of important information from
> the standard that I don't want to read myself.
>
As far as I'm concerned, nothing beats a good glass of red wine whilst
reading the SQL standard documents in fron
Thanks Lukas!
It is always a pleasure to get these nuggets of important information
from the standard that I don't want to read myself.
Could you give me an example of what you can but in the having clause if
group by does not have any columns?
- Rami
On 26.10.2015 22:57, Lukas Eder wrote:
Folks, I must chime in here. :)
Let me quote from the SQL:2011 standard:
7.10
Syntax Rules
1) Let HC be the . Let TE be the that
immediately contains HC. If TE
does not immediately contain a , then “GROUP BY ()” is
implicit.
There is no doubt about the fact that HAVING without GROUP BY is
Hi,
> supported by major rdbms
No. It only "works" with MySQL, and there, I guess it's just for backward
compatibility (I didn't test with ANSI mode). The test also fails with
Oracle and MS SQL Server (using SQLFiddle). So the MS SQL Server
documentation is actually wrong there.
Regards,
Thomas
Rami,
According to http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt (sec
7.8)
HAVING filters results of the preceding , , or
. If that clause is not a , then result
consists of a single group and does not have a grouping column.
Its long story to explain my case. In general API returns
AFAIK having is a feature that allows the user to apply to results of
group by.
The order of sql statement is as follows:
- from
- where
- group by
- having
- select clause
- order by
If I am correct my question to Taras is what do you mean when you want
to use having without group by?
Can't y
Thomas,
Please describe in which cases HAVING without GROUP BY is allowed. Current
documentation has no information about it.
Unfortunately you didn't mention Oracle, MySQL, MS that support this
feature and are the most popular rdbms. SQL 92 allows it, too.
Thank you,
Taras
On Wednesday, Oct
How HAVING should be handled seems to be a moving target, as can be seen
with this MS post for MSQL
https://msdn.microsoft.com/en-us/library/ms180199.aspx
"When GROUP BY is not used, HAVING behaves like a WHERE clause"
I know Oracle and PostgreSQL will accept certain HAVING expressions wi
Hi,
Your example works with H2 (unfortunately), but this one fails (it throws
an exception):
drop table table_test;
CREATE TABLE TABLE_TEST(account_id BIGINT, time BIGINT);
INSERT INTO TABLE_TEST (account_id,time) VALUES (10,2);
INSERT INTO TABLE_TEST (account_id,time) VALUES (10,3);
SELECT acco
Hi,
H2(v1.4.190) throws error if HAVING is used without GROUP BY.
Ex:
CREATE MEMORY TABLE IF NOT EXISTS TABLE_TEST(account_id BIGINT, time
BIGINT) NOT PERSISTENT
INSERT INTO TABLE_TEST (account_id,time) VALUES (10,2)
SELECT account_id, time FROM TABLE_TEST HAVING account_id=10 //throws
org.h2.jd
18 matches
Mail list logo
