Group by and having count weird-CodePudding

I have this table

R1(a,b,c,d,e,f)
AS
(
  SELECT MIN(R.A1),MAX(R.A2),1, COUNT(R.A4),2 SUM(R.A5),0
  FROM R
  GROUP BY R.A6
  HAVING SUM(R.A3)>2
),

R2(a,b,c,d,e,f)
AS
(
  SELECT 1,0, MAX(R.A3),SUM(R.A4), COUNT(R.A5),2 SUM(R.A6)
  FROM R
  GROUP BY R.A2
  HAVING SUM(R.A1)<3
),

R3 AS 
(
  SELECT *FROM R1 UNINON SELECT * FROM R2
)
SELECT SUM(d),SUM(e)
FROM R3

based on this sql code

Question:What is the product X,Y that gives result (11,11)?

What I did is :

from R1 I have COUNT(R.A4) it makes 3 X ???

  2 SUM(R.A5) it makes 2   Y

From R2

SUM(R.A4) = 1 X

COUNT(R.A5)=8 Y

So,

SELECT SUM(d), SUM(e)
FROM R3

produces 4 X and 8 Y.

what I found is ,x=7 and Y=3 so I will have (11,11) I am not sure

I am feeling something I am doing wrong...

editted more: what i think is-not sure GROUP BY R.A2 HAVING SUM(R.A1)<3 // I go to A1 and look 0 0 = 0 it is smaller than the 3 so I take the results 0 from A1 which are 0 from the table A2.As a result I have 2 rows COUNT(R.A5)=2 Y.

CodePudding user response：

In a select statement containing a GROUP BY the WHERE clause will filter what rows get processed by the GROUP BY. The HAVING clause will filter what records are returned by the query.

So GROUP BY R.A2 HAVING SUM(R.A1)<3 will perform a GROUP BY on the distinct values of R.A2 and then if the SUM of the values in the A1 column are greater than or equal to 3 they will not be returned in the results.