Question 20019 in Mysql Question posted in
The official MySQL documentation can be found here.

What does count(*)=1 do? – Mysql

SELECT 
    AL1.ACCOUNTING_PERIOD, 
    AL1.BUSINESS_AREA, 
    AL1.SAP_ACCOUNT, 
    AL1.ID, 
    AL1.USD_AMT, 
    AL1.BAN_NBR, 
    AL2.CUST_NBR, 
    AL2.CUST_NAME
FROM
    DSL_AIM.AIM AL1,
    DSL_AML.AID AL2 
WHERE
    (
        (
            AL1.ACCOUNTING_PERIOD_CD IN ( '2023-02' )

GROUP BY
    AL1.ACCOUNTING_PERIOD,
    AL1.BUSINESS_AREA,
    AL1.SAP_ACCOUNT,
    AL1.ID,
    AL1.USD_AMT,
    AL1.BAN_NBR,
    AL2.CUST_NBR,
    AL2.CUST_NAME

HAVING
    COUNT(*) = 1;

I get a different amount of line items when I have the having statement and when I don’t, so what does HAVING COUNT(*) = 1 mean/do in this context?

Tags:

2

Answers


  1. 0

    what does HAVING COUNT(*) = 1 mean/do in this context?

    It returns all the rows where there is not a duplicate with the same values.

    For example, given the sample data:

    CREATE TABLE table_name (x, y, z) AS
SELECT 1, 1, 1 FROM DUAL UNION ALL
SELECT 2, 2, 2 FROM DUAL UNION ALL
SELECT 2, 2, 2 FROM DUAL UNION ALL
SELECT 3, 3, 3 FROM DUAL UNION ALL
SELECT 3, 4, 5 FROM DUAL;

    Then the similar query:

    SELECT x, y, z
FROM   table_name
GROUP BY x, y, z
HAVING COUNT(*) = 1;

    Outputs:

    X Y Z
    1 1 1
    3 3 3
    3 4 5

    The 2, 2, 2 row is not included as there are two instances of the row and so COUNT(*) = 2 and not 1 for that group; the other rows are included as they are all unique.

    fiddle

    Login or Signup to reply.
  2. 0

    Query seems to be wrong altogether; there’s no join between tables so you’re producing Cartesian product, and then filter it on accounting period. Besides, where clause doesn’t require parenthesis, and you have two of them here: where (( which aren’t closed.

    SELECT 
AL1.ACCOUNTING_PERIOD, 
AL1.BUSINESS_AREA, 
AL1.SAP_ACCOUNT, 
AL1.ID, 
AL1.USD_AMT, 
AL1.BAN_NBR, 
AL2.CUST_NBR, 
AL2.CUST_NAME
COUNT(*)                                   --> add this
FROM DSL_AIM.AIM AL1, DSL_AML.AID AL2 
WHERE ((AL1.ACCOUNTING_PERIOD_CD IN ('2023-02')
GROUP BY AL1.ACCOUNTING_PERIOD, AL1.BUSINESS_AREA, AL1.SAP_ACCOUNT, AL1.ID, AL1.USD_AMT, AL1.BAN_NBR, AL2.CUST_NBR, AL2.CUST_NAME

    you’d see number of rows that share the same set of values in all non-aggregated columns (i.e. all columns specified in GROUP BY clause).

    Then, you can add the HAVING clause to further filter result set. In your case, it is having count(*) = 1, which means that there’s only one row per combination.

    It could have been e.g. having count(*) > 1, which means that you’d fetch rows that represent "duplicates".

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search