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

Mysql count if the value less or greater than other value

SELECT wilayah,nilai,t_rkpd,tahun,periode
FROM nilai_indikator
INNER JOIN provinsi ON nilai_indikator.wilayah=provinsi.id
WHERE tahun='2022' AND periode='08' AND id_indikator='6'

When i query that code the result is like this
table
I try count if the nilai is less or greater than t_rkpd, using this code:

SELECT COUNT(CASE WHEN nilai > t_rkpd THEN 1 END) AS tercapai 
FROM nilai_indikator
INNER JOIN provinsi ON nilai_indikator.wilayah=provinsi.id
WHERE tahun='2022' AND periode='08' AND id_indikator='6'

SELECT COUNT(CASE WHEN nilai < t_rkpd THEN 1 END) AS tidak_tercapai
FROM nilai_indikator
INNER JOIN provinsi ON nilai_indikator.wilayah=provinsi.id
WHERE tahun='2022' AND periode='08' AND id_indikator='6'

But the result both of them is 0, it should be 34 when nilai greater then t_rkpd. why this is happening?

Tags:

3

Answers


  1. 0

    Try the same code using SUM instead of Count and let me know if it works.

    SELECT ...,SUM(CASE WHEN nilai > t_rkpd THEN 1 ELSE 0 END) AS tercapai 
FROM nilai_indikator
INNER JOIN provinsi ON nilai_indikator.wilayah=provinsi.id
WHERE tahun='2022' AND periode='08' AND id_indikator='6'
GROUP BY ...

    And you need to group by some column as there is an aggregate. Do let me know if this helps

    Login or Signup to reply.
  2. 0

    Assuming that NULL means 0. You just need to add a condition..

    SELECT COUNT(CASE WHEN t_rkpd IS NULL OR nilai > t_rkpd  THEN 1 END) AS tercapai 
FROM nilai_indikator
INNER JOIN provinsi ON nilai_indikator.wilayah=provinsi.id
WHERE tahun='2022' AND periode='08' AND id_indikator='6'
    Login or Signup to reply.
  3. 0

    NULL means "unknown value". So, if you compare nilai with t_rkpd and one of the values is NULL then the answer is "unknown".

    You have COUNT(CASE WHEN nilai > t_rkpd THEN 1 END). The THEN part gets executed when the expression nilai > t_rkpd is true. There is no ELSE in the expression, so the DBMS defaults to ELSE NULL. This part gets executed when nilai > t_rkpd is not true, namely false or unknown.

    This means, when nilai or t_rkpd are null, you get a null as an expression result, and COUNT ignores this row hence.

    In the following query we consider all rows and all comparison results:

    SELECT result, COUNT(*)
FROM
(
  SELECT
    CASE
      WHEN ni.nilai > p.t_rkpd THEN 'nilai > t_rkpd'
      WHEN ni.nilai < p.t_rkpd THEN 'nilai < t_rkpd'
      WHEN ni.nilai = p.t_rkpd THEN 'nilai = t_rkpd'
      ELSE 'unknown'
    END as result
  FROM nilai_indikator ni
  INNER JOIN provinsi p ON ni.wilayah = p.id
  WHERE ni.tahun = 2022
  AND ni.periode = 8 
  AND ni.id_indikator = 6
) compared
GROUP BY result
ORDER BY result;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search