What does “expr” do in MySQL's COUNT(expr) function?

-1

Consider below:

SELECT COUNT(*) FROM table;

SELECT COUNT(1) FROM table;

SELECT COUNT(-2) FROM table;

SELECT COUNT(135392) FROM table;

SELECT COUNT(field) FROM table;

SELECT COUNT(field1 + field2) FROM table;

I am not clear on what expr actually does, or what it can be used for because all of the above SQL statements return the same result. Example below:

+-----------+

| count(..) |

+-----------+

|     54542 |

+-----------+

MySQL's manual (https://dev.mysql.com/doc/refman/8.0/en/counting-rows.html) does not go into very much detail on the expr part, other than using the * symbol

asked Nov 28 '18 at 19:02

Dennis

3,19843177

The doc link you mentioned is incomplete. This would be a complete one (also referenced in your source link): dev.mysql.com/doc/refman/8.0/en/…

– Madhur Bhaiya
Nov 28 '18 at 19:07

add a comment |

-1

Consider below:

SELECT COUNT(*) FROM table;

SELECT COUNT(1) FROM table;

SELECT COUNT(-2) FROM table;

SELECT COUNT(135392) FROM table;

SELECT COUNT(field) FROM table;

SELECT COUNT(field1 + field2) FROM table;

I am not clear on what expr actually does, or what it can be used for because all of the above SQL statements return the same result. Example below:

+-----------+

| count(..) |

+-----------+

|     54542 |

+-----------+

MySQL's manual (https://dev.mysql.com/doc/refman/8.0/en/counting-rows.html) does not go into very much detail on the expr part, other than using the * symbol

asked Nov 28 '18 at 19:02

Dennis

3,19843177

The doc link you mentioned is incomplete. This would be a complete one (also referenced in your source link): dev.mysql.com/doc/refman/8.0/en/…

– Madhur Bhaiya
Nov 28 '18 at 19:07

add a comment |

-1

Consider below:

SELECT COUNT(*) FROM table;

SELECT COUNT(1) FROM table;

SELECT COUNT(-2) FROM table;

SELECT COUNT(135392) FROM table;

SELECT COUNT(field) FROM table;

SELECT COUNT(field1 + field2) FROM table;

I am not clear on what expr actually does, or what it can be used for because all of the above SQL statements return the same result. Example below:

+-----------+

| count(..) |

+-----------+

|     54542 |

+-----------+

MySQL's manual (https://dev.mysql.com/doc/refman/8.0/en/counting-rows.html) does not go into very much detail on the expr part, other than using the * symbol

asked Nov 28 '18 at 19:02

Dennis

3,19843177

Consider below:

SELECT COUNT(*) FROM table;

SELECT COUNT(1) FROM table;

SELECT COUNT(-2) FROM table;

SELECT COUNT(135392) FROM table;

SELECT COUNT(field) FROM table;

SELECT COUNT(field1 + field2) FROM table;

I am not clear on what expr actually does, or what it can be used for because all of the above SQL statements return the same result. Example below:

+-----------+

| count(..) |

+-----------+

|     54542 |

+-----------+

MySQL's manual (https://dev.mysql.com/doc/refman/8.0/en/counting-rows.html) does not go into very much detail on the expr part, other than using the * symbol

mysql sql database count

asked Nov 28 '18 at 19:02

Dennis

3,19843177

asked Nov 28 '18 at 19:02

Dennis

3,19843177

asked Nov 28 '18 at 19:02

Dennis

3,19843177

asked Nov 28 '18 at 19:02

Dennis

3,19843177

asked Nov 28 '18 at 19:02

Dennis

3,19843177

The doc link you mentioned is incomplete. This would be a complete one (also referenced in your source link): dev.mysql.com/doc/refman/8.0/en/…

– Madhur Bhaiya
Nov 28 '18 at 19:07

add a comment |

The doc link you mentioned is incomplete. This would be a complete one (also referenced in your source link): dev.mysql.com/doc/refman/8.0/en/…

– Madhur Bhaiya
Nov 28 '18 at 19:07

The doc link you mentioned is incomplete. This would be a complete one (also referenced in your source link): dev.mysql.com/doc/refman/8.0/en/…

– Madhur Bhaiya
Nov 28 '18 at 19:07

add a comment |

3 Answers
3

active

oldest

votes

COUNT(<expr>) counts the number of rows where <expr> evaluates to a non-NULL values.

Generally, it is not needed with an expression and would only be used on a single, NULL-able column -- or column that could be NULL due to an outer join.

answered Nov 28 '18 at 19:03

Gordon Linoff

792k36316419

add a comment |

COUNT(*) will count all rows

COUNT(expr) will count the row if expr is NOT NULL

So COUNT(expr) could be less than COUNT(*) if expr contains NULL values:

SELECT COUNT(*), COUNT(1), COUNT(col)

FROM (

    SELECT 'a' UNION ALL

    SELECT 'b' UNION ALL

    SELECT NULL

) AS t(col)



-- 3    3    2

edited Nov 28 '18 at 19:20

answered Nov 28 '18 at 19:04

Salman A

185k67343441

add a comment |

Expr is short for expression, itself short for “some valid block of sql that results in a single value for this row when evaluated”

It could be a constant, a column, the result of a function call, variable assignment, case statement etc

—equivalent

COUNT(*)

COUNT(1)

COUNT(‘a’)



—count only males. If the group is 1000 in number and 600 are female, this returns 400

COUNT(case when gender = ‘m’ then ‘a’ else null end)

In supplement to the other answers, the <expr> may optionally start with the word DISTINCT in which case only unique occurrences of the referenced entity/expression/function result are counted

—in a set of 1000 animals, returns 1000

COUNT(gender)



—in a set of 1000 animals, 600 female, returns 2 (only values M and F exist in the group)

COUNT(distinct gender)

edited Nov 28 '18 at 21:17

answered Nov 28 '18 at 20:41

Caius Jard

12.5k21340

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53526372%2fwhat-does-expr-do-in-mysqls-countexpr-function%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

3 Answers
3

active

oldest

votes

3 Answers
3

active

oldest

votes

COUNT(<expr>) counts the number of rows where <expr> evaluates to a non-NULL values.

Generally, it is not needed with an expression and would only be used on a single, NULL-able column -- or column that could be NULL due to an outer join.

answered Nov 28 '18 at 19:03

Gordon Linoff

792k36316419

add a comment |

COUNT(<expr>) counts the number of rows where <expr> evaluates to a non-NULL values.

Generally, it is not needed with an expression and would only be used on a single, NULL-able column -- or column that could be NULL due to an outer join.

answered Nov 28 '18 at 19:03

Gordon Linoff

792k36316419

add a comment |

COUNT(<expr>) counts the number of rows where <expr> evaluates to a non-NULL values.

Generally, it is not needed with an expression and would only be used on a single, NULL-able column -- or column that could be NULL due to an outer join.

answered Nov 28 '18 at 19:03

Gordon Linoff

792k36316419

COUNT(<expr>) counts the number of rows where <expr> evaluates to a non-NULL values.

Generally, it is not needed with an expression and would only be used on a single, NULL-able column -- or column that could be NULL due to an outer join.

answered Nov 28 '18 at 19:03

Gordon Linoff

792k36316419

answered Nov 28 '18 at 19:03

Gordon Linoff

792k36316419

answered Nov 28 '18 at 19:03

Gordon Linoff

792k36316419

answered Nov 28 '18 at 19:03

Gordon Linoff

792k36316419

add a comment |

COUNT(*) will count all rows

COUNT(expr) will count the row if expr is NOT NULL

So COUNT(expr) could be less than COUNT(*) if expr contains NULL values:

SELECT COUNT(*), COUNT(1), COUNT(col)

FROM (

    SELECT 'a' UNION ALL

    SELECT 'b' UNION ALL

    SELECT NULL

) AS t(col)



-- 3    3    2

edited Nov 28 '18 at 19:20

answered Nov 28 '18 at 19:04

Salman A

185k67343441

add a comment |

COUNT(*) will count all rows

COUNT(expr) will count the row if expr is NOT NULL

So COUNT(expr) could be less than COUNT(*) if expr contains NULL values:

SELECT COUNT(*), COUNT(1), COUNT(col)

FROM (

    SELECT 'a' UNION ALL

    SELECT 'b' UNION ALL

    SELECT NULL

) AS t(col)



-- 3    3    2

edited Nov 28 '18 at 19:20

answered Nov 28 '18 at 19:04

Salman A

185k67343441

add a comment |

COUNT(*) will count all rows

COUNT(expr) will count the row if expr is NOT NULL

So COUNT(expr) could be less than COUNT(*) if expr contains NULL values:

SELECT COUNT(*), COUNT(1), COUNT(col)

FROM (

    SELECT 'a' UNION ALL

    SELECT 'b' UNION ALL

    SELECT NULL

) AS t(col)



-- 3    3    2

edited Nov 28 '18 at 19:20

answered Nov 28 '18 at 19:04

Salman A

185k67343441

COUNT(*) will count all rows

COUNT(expr) will count the row if expr is NOT NULL

So COUNT(expr) could be less than COUNT(*) if expr contains NULL values:

SELECT COUNT(*), COUNT(1), COUNT(col)

FROM (

    SELECT 'a' UNION ALL

    SELECT 'b' UNION ALL

    SELECT NULL

) AS t(col)



-- 3    3    2

edited Nov 28 '18 at 19:20

answered Nov 28 '18 at 19:04

Salman A

185k67343441

edited Nov 28 '18 at 19:20

answered Nov 28 '18 at 19:04

Salman A

185k67343441

answered Nov 28 '18 at 19:04

Salman A

185k67343441

answered Nov 28 '18 at 19:04

Salman A

185k67343441

add a comment |

Expr is short for expression, itself short for “some valid block of sql that results in a single value for this row when evaluated”

It could be a constant, a column, the result of a function call, variable assignment, case statement etc

—equivalent

COUNT(*)

COUNT(1)

COUNT(‘a’)



—count only males. If the group is 1000 in number and 600 are female, this returns 400

COUNT(case when gender = ‘m’ then ‘a’ else null end)

In supplement to the other answers, the <expr> may optionally start with the word DISTINCT in which case only unique occurrences of the referenced entity/expression/function result are counted

—in a set of 1000 animals, returns 1000

COUNT(gender)



—in a set of 1000 animals, 600 female, returns 2 (only values M and F exist in the group)

COUNT(distinct gender)

edited Nov 28 '18 at 21:17

answered Nov 28 '18 at 20:41

Caius Jard

12.5k21340

add a comment |

Expr is short for expression, itself short for “some valid block of sql that results in a single value for this row when evaluated”

It could be a constant, a column, the result of a function call, variable assignment, case statement etc

—equivalent

COUNT(*)

COUNT(1)

COUNT(‘a’)



—count only males. If the group is 1000 in number and 600 are female, this returns 400

COUNT(case when gender = ‘m’ then ‘a’ else null end)

In supplement to the other answers, the <expr> may optionally start with the word DISTINCT in which case only unique occurrences of the referenced entity/expression/function result are counted

—in a set of 1000 animals, returns 1000

COUNT(gender)



—in a set of 1000 animals, 600 female, returns 2 (only values M and F exist in the group)

COUNT(distinct gender)

edited Nov 28 '18 at 21:17

answered Nov 28 '18 at 20:41

Caius Jard

12.5k21340

add a comment |

Expr is short for expression, itself short for “some valid block of sql that results in a single value for this row when evaluated”

It could be a constant, a column, the result of a function call, variable assignment, case statement etc

—equivalent

COUNT(*)

COUNT(1)

COUNT(‘a’)



—count only males. If the group is 1000 in number and 600 are female, this returns 400

COUNT(case when gender = ‘m’ then ‘a’ else null end)

In supplement to the other answers, the <expr> may optionally start with the word DISTINCT in which case only unique occurrences of the referenced entity/expression/function result are counted

—in a set of 1000 animals, returns 1000

COUNT(gender)



—in a set of 1000 animals, 600 female, returns 2 (only values M and F exist in the group)

COUNT(distinct gender)

edited Nov 28 '18 at 21:17

answered Nov 28 '18 at 20:41

Caius Jard

12.5k21340

Expr is short for expression, itself short for “some valid block of sql that results in a single value for this row when evaluated”

It could be a constant, a column, the result of a function call, variable assignment, case statement etc

—equivalent

COUNT(*)

COUNT(1)

COUNT(‘a’)



—count only males. If the group is 1000 in number and 600 are female, this returns 400

COUNT(case when gender = ‘m’ then ‘a’ else null end)

In supplement to the other answers, the <expr> may optionally start with the word DISTINCT in which case only unique occurrences of the referenced entity/expression/function result are counted

—in a set of 1000 animals, returns 1000

COUNT(gender)



—in a set of 1000 animals, 600 female, returns 2 (only values M and F exist in the group)

COUNT(distinct gender)

edited Nov 28 '18 at 21:17

answered Nov 28 '18 at 20:41

Caius Jard

12.5k21340

edited Nov 28 '18 at 21:17

answered Nov 28 '18 at 20:41

Caius Jard

12.5k21340

answered Nov 28 '18 at 20:41

Caius Jard

12.5k21340

answered Nov 28 '18 at 20:41

Caius Jard

12.5k21340

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Btukfyl