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

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.

Adam


I have been know to use GROUP BY __RowID

Note the two underscores.

Roy Lambert

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

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

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

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

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