how to count subquery in mysql

Question

i have this query :

Select
  DATE_FORMAT(calls.call_datetime, "%Y-%m-%d") as `groupdate`
from
  calls
WHERE calls.job_id =1
group by DATE_FORMAT(calls.call_datetime,
"%Y-%m-%d")

with return :

groupdate

2018-07-06

2018-07-06

there are 2 rows. how do I count the above query so that the result becomes '2' with one row ?

what i want is:

Result

2

thanks, please help me

Possible duplicate of [How to use count and group by at the same select statement](https://stackoverflow.com/questions/2722408/how-to-use-count-and-group-by-at-the-same-select-statement) — Alexander, Jul 07 '18 at 16:11
@Alexander - No, That is not the same question. True, the second best answer to that question is correct for _this_ question, but it is wrong for _that_ question. — Rick James, Jul 07 '18 at 20:00

score 1 · Answer 1 · answered Jul 07 '18 at 16:09

1

try:

Select
  Count(DATE_FORMAT(calls.call_datetime, "%Y-%m-%d")) as `groupdate`
from
  calls
WHERE calls.job_id =1
group by DATE_FORMAT(calls.call_datetime,
"%Y-%m-%d")

answered Jul 07 '18 at 16:09

Coral Kashri

3,436
2
10
22

score 0 · Answer 2 · answered Jul 07 '18 at 16:09

0

You should use count()

Select DATE_FORMAT(calls.call_datetime, "%Y-%m-%d") as groupdate , count(*)
from calls 
WHERE calls.job_id =1 
group by DATE_FORMAT(calls.call_datetime, "%Y-%m-%d")

answered Jul 07 '18 at 16:09

ScaisEdge

131,976
10
91
107

Raymond Nijland · Accepted Answer · 2018-07-07T16:18:22.273

Most simple would be to use

Select
  COUNT(*) as Result
from
  calls
WHERE calls.job_id =1
group by DATE_FORMAT(calls.call_datetime, "%Y-%m-%d")

or a delivered table approach

SELECT
 COUNT(*) as Result
FROM (
  Select
    DATE_FORMAT(calls.call_datetime, "%Y-%m-%d") as `groupdate`
  from
    calls
  WHERE calls.job_id =1 
  group by DATE_FORMAT(calls.call_datetime, "%Y-%m-%d")
) AS a

Both queries should return the same expected result you want.

score 0 · Answer 4 · answered Jul 07 '18 at 20:04

The question is ambiguous. Is the desired answer "2" because there are 2 rows for 1 date? What if there were more than one date?

SELECT COUNT(*) FROM calls WHERE ...; -- delivers "2"

SELECT COUNT(*) FROM calls WHERE ...; GROUP BY DATE(datetime) -- also delivers "2"

SELECT COUNT(DISTINCT DATE(call_datetime)) FROM calls WHERE ...; -- delivers "1"

how to count subquery in mysql

4 Answers4