Multiple Counts in MYSQL query

Question

I would like to count a specific database column with TWO different count criteria so i can calculate a percentage

For example lets same the database column is called Latency, and the data in the column contains the number of seconds.

I would like to count the number of Latency messages > 50 seconds but also the TOTAL number of Latency messages in the same query ( i.e. > 0 seconds)

My current query for > 50 seconds is as follows

select COUNT(*) from table WHERE date = 20180701 and CAST(Latency As Signed) > 50

How would i include counts > 0 seconds in the same query ??

Possible duplicate of [How to get multiple counts with one SQL query?](https://stackoverflow.com/questions/12789396/how-to-get-multiple-counts-with-one-sql-query) — Nick, Sep 20 '18 at 03:03

Tim Biegeleisen · Accepted Answer · 2018-09-20T05:14:12.200

1

Use conditional aggregation:

SELECT
    COUNT(CASE WHEN CAST(Latency As Signed) > 50 THEN 1 END) AS latency,
    COUNT(CASE WHEN CAST(Latency As Signed) > 0  THEN 1 END) AS total,
    100.0 * COUNT(CASE WHEN CAST(Latency As Signed) > 50 THEN 1 END) /
            COUNT(CASE WHEN CAST(Latency As Signed) > 0  THEN 1 END) AS percentage
FROM table
WHERE date = 20180701;

edited Sep 20 '18 at 05:14

answered Sep 20 '18 at 02:57

Tim Biegeleisen

502,043
27
286
360

Going one step further , is it possible to calculate a "percentage" of total / latency in the sql statement ? – John Sep 20 '18 at 05:12
@John Just take the ratios of the two conditional counts (I think). – Tim Biegeleisen Sep 20 '18 at 05:14

score 0 · Answer 2 · answered Sep 20 '18 at 03:02

0

This maybe helpful to you, @John, How to get multiple counts with one SQL query? It uses count together with aggregate functions in order to target multiple count function in one query..

answered Sep 20 '18 at 03:02

aries

64
1
12

1

Hi Aries, when you find a duplicate like this it's better to just flag the question as a duplicate instead (use the `flag` link underneath the question tags). – Nick Sep 20 '18 at 03:05
1

Thanks for that, @Nick I'll look for that helpful tip.. :) – aries Sep 20 '18 at 03:10

Multiple Counts in MYSQL query

2 Answers2