Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 8 of 8 total |
HAVING clause without the GROUP Clause |
Sat, Oct 30 2021 4:37 PM | Permanent Link |
Richard Harding Wise Nutrition Coaching | The SQL standards allow the HAVING clause to included with the SELECT statement without the GROUP clause.
Is this something that can be easily included in the next release? It is really useful for correlated subqueries. It can be difficult to find an alternative solution without using the GROUP clause. ------------------------------------- 7.10 <having clause> Function Specify a grouped table derived by the elimination of groups that do not satisfy a <search condition>. Format <having clause> ::= HAVING <search condition> Syntax Rules 1) Let HC be the <having clause>. Let TE be the <table expression> that immediately contains HC. If TE does not immediately contain a <group by clause>, then “GROUP BY ()” is implicit. Let T be the descriptor of the table defined by the <group by clause> GBC immediately contained in TE and let R be the result of GBC. Thank you Richard |
Wed, Nov 24 2021 12:00 PM | Permanent Link |
Adam Brett Orixa Systems | Dear Richard
It is also pretty easy to do a "GROUP BY" which doesn't actually GROUP anything, ie add Primary Key fields to the GROUP BY clause until even though the results are grouped you still see all the rows. I guess this is a kludge, but it works fine. |
Thu, Nov 25 2021 6:43 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Adam
I have been know to use GROUP BY __RowID Note the two underscores. Roy Lambert |
Thu, Nov 25 2021 8:44 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Richard
After my initial post I re-read yours and I became curious so started searching. My first hit - https://www.w3schools.com/sql/sql_having.asp - gave me this The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions. The next one - https://www.educba.com/sql-having-clause/ - this ‘Having’ clause in SQL is used for aggregation operations along with ‘Where’, ‘group by’ & ‘order by’ condition statements. It is applied on a table/ database where there is a need for filtering aggregate results and allows ‘group by’ and ‘order by’ conditions. When the ‘having’ clause is used in a query, it is expected that the resulting output data set can have more than one record from the table/ database. Both of which lead me to ask a question - what use is HAVING without a "proper" GROUP BY? Roy Lambert |
Wed, Dec 1 2021 3:22 PM | Permanent Link |
Richard Harding Wise Nutrition Coaching | Greetings Roy
<<Both of which lead me to ask a question - what use is HAVING without a "proper" GROUP BY?>> The simple answer is correlated sub-queries. I cannot give examples where it is useful because I have either done it another way or ignored the problem. Richard |
Wed, Dec 1 2021 3:34 PM | Permanent Link |
Richard Harding Wise Nutrition Coaching | Greetings Roy
<<Both of which lead me to ask a question - what use is HAVING without a "proper" GROUP BY?>> The simple answer is correlated sub-queries. I cannot give examples where it is useful because I have either done it another way or ignored the problem. I will let you know if I find an example. Richard |
Fri, Dec 3 2021 8:03 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Richard,
<< The SQL standards allow the HAVING clause to included with the SELECT statement without the GROUP clause. Is this something that can be easily included in the next release? >> Please post an example of what you're trying to do. ElevateDB does allow the use of HAVING without a GROUP BY, but only if you're generating a single row result set to begin with (all SELECT columns are either aggregates or constants). Tim Young Elevate Software www.elevatesoft.com |
Tue, Dec 14 2021 11:11 PM | Permanent Link |
Richard Harding Wise Nutrition Coaching | Tim
Unless it is really very easy to implement the HAVING clause without the GROUP clause, I do not think that is worth the effort. It is part of the SQL standard but not all SQLs support it. Sometimes it is useful for correlated sub-queries. It is not always obvious (at least to me) what an alternative is. Example below. Another example where it may be useful is for finding which hospital in each STATE and REGION has the highest prevalence of some condition. The China-Cornell-Oxford project conducted surveys in China in 1983–1984 and 1989–1990. The study consisted of 6,500 people in 65 counties. In each county, two xiang were selected with 25 men and 25 women from different families selected from each xiang. 365 blood, urine, food samples, lifestyle factors and geographical variables were obtained. The variable P001 is Total Cholesterol. The following query show the villages with the two lowest cholesterol measurements in each district for females and males. A district consists of up to 6 counties. In the 1980s, it was commonly believed that low cholesterol had significant health risks. Some popular commentators still advocate this. This study showed that our bodies make sufficient cholesterol and in normal circumstances it is impossible to have insufficient cholesterol. The query show which villages have the lowest two cholesterol readings which can help determine if there is any detrimental health outcomes as a result and if there are any significant correlations that may affect this. CREATE TABLE "CH89PRUz" ( "District" VARCHAR(1) COLLATE "ANSI_CI", -- LEFT(County, 1) "County" VARCHAR(2) COLLATE "ANSI_CI", "Xiang" INTEGER, -- 1 or 2 "Sex" VARCHAR(1) COLLATE "ANSI_CI", -- 'F' or 'M' "ProvinceCode" VARCHAR(2) COLLATE "ANSI_CI" NOT NULL, "ProvinceName" VARCHAR(64) COLLATE "ANSI_CI" NOT NULL, "CSProvinceCode" VARCHAR(1) COLLATE "ANSI_CI", "Village" VARCHAR(4) COLLATE "ANSI_CI", -- County '-' || CAST(xiang AS VARCHAR(1)) "P001" DECIMAL(19,4), CONSTRAINT "ch83pur_pri" PRIMARY KEY ("County", "Xiang", "Sex") ) The following does not work because it does not have a GROUP clause. CREATE VIEW "acsP001Min2a_3Countries" AS --example 1 - Does not work - No GROUP clause SELECT DISTINCT District, County, Xiang, Village, ProvinceName, Sex, P001 FROM CH89PRUz C0 WHERE P001 IN (SELECT C1.P001 FROM CH89PRUz C1 WHERE C0.District = C1.District AND C0.Sex = C1.Sex AND C0.P001 >= C1.P001 HAVING COUNT(*) <= 2) ORDER BY District, Sex, P001 ASC This works. CREATE VIEW "acsP001Min2" AS --example 3 - 110 rows SELECT DISTINCT County, Xiang, Village, ProvinceName, Sex, P001 FROM CH89PRUz C0 INNER JOIN Counties X0 ON C0.ProvinceCode = X0.ProvinceCode WHERE C0.P001 <= (SELECT MIN(C1.P001) FROM CH89PRUz C1 WHERE C0.County = C1.County AND C0.Sex = C1.Sex AND C0.P001 >= C0.P001 HAVING COUNT(DISTINCT C1.P001) <= 2) ORDER BY ProvinceCode, Sex, P001 In DISTRICT J, the villages with the 2 lowest CHOL rankings (taking into account duplicates values) are: "District"|"County"|"Xiang"|"Village"|"ProvinceName"|"Sex"|"P001" "J"|"JB"|1|"JB-1"|"Anhui Province"|"F"|127 -District J - village JB-1 is ranked 1 (females) "J"|"JA"|2|"JA-2"|"Anhui Province"|"F"|137 -District J - village JA-2 is ranked 2 (females) "J"|"JA"|1|"JA-1"|"Anhui Province"|"M"|129 -District J - village JA-1 is ranked 1 (males) "J"|"JB"|1|"JB-1"|"Anhui Province"|"M"|129 -District J - village JB-1 is ranked 1 (males) "J"|"JA"|2|"JA-2"|"Anhui Province"|"M"|138 -District J - village JA-2 is ranked 2 (males) There was a strong correlation between cholesterol and mortality rates from heart disease and sex-hormone related cancers (breast, endometrial, ovarian and prostate cancers) even though these mortality rates were (and still are) a fraction of those in Australia and the USA. In the 1980s in the USA and Australia, "normal" cholesterol was about 300 mg/dL (7.8 mmol/L). The Japanese Ministry of Health has maintained an extensive database of diet and health statistics since the end of World War 2. The 47 Prefectures of Japan have been organised in to 12 dietary regions. There is substantial variations in Japanese diet and health due to regional variations and also due to the diet becoming more "western" and homogeneous in recent decades. This is a useful source of dietary and health data. Richard |
This web page was last updated on Wednesday, May 15, 2024 at 08:40 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |