CODEWITHSUNDEEP
mysqlsql
dewinataria
dewinataria

Reputation: 97

Why do I get error in SQL query?

I made a query from view table in phpMyAdmin. When I tried to do query, the result was error. Here is my query:

SELECT TahunMasuk, 
  COUNT(CASE WHEN `JenisKelamin`=`Perempuan`) AS `Perempuan`,
  COUNT(CASE WHEN `JenisKelamin`=`Laki-laki`) AS `Laki-laki`
FROM jeniskelaminreal
GROUP BY TahunMasuk;

The error message is

#1054 - Unknown column 'Perempuan' in 'field list'

I use left join in my view table. Here is my view table named jeniskelaminreal

enter image description here

What may I do so that I can get the result? Thank you in advance

Upvotes: 0

Views: 122

Answers (6)

denny
denny

Reputation: 2254

as per mysql documentation the syntax is

CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] ...
[ELSE statement_list]
END CASE

or

CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] ...
[ELSE statement_list]
END CASE

READ HERE

your query look like this 

SELECT TahunMasuk, 
COUNT(CASE WHEN `JenisKelamin`='Perempuan' THEN 1 END) AS 'Perempuan',
COUNT(CASE WHEN `JenisKelamin`='Laki-laki' THEN 1 END) AS 'Laki-laki'
FROM jeniskelaminreal
GROUP BY TahunMasuk;

and also you can do this way by using field() with sum() to count those results

SELECT TahunMasuk, 
SUM(FIELD(JenisKelamin,Perempuan)) AS 'Perempuan',
SUM(FIELD(JenisKelamin,Laki-laki)) AS 'Laki-laki'
FROM jeniskelaminreal
GROUP BY TahunMasuk;

Upvotes: 0

4EACH
4EACH

Reputation: 2197

Replace ` with ' in 'Perempuan' and 'Laki-laki'

SELECT TahunMasuk, 
  COUNT(CASE WHEN `JenisKelamin`='Perempuan' THEN 1 END) AS 'Perempuan',
  COUNT(CASE WHEN `JenisKelamin`='Laki-laki' THEN 1 END) AS 'Laki-laki'
FROM jeniskelaminreal
GROUP BY TahunMasuk;

Upvotes: 1

Nik Lakhani
Nik Lakhani

Reputation: 217

CASE statement is missing ELSE part and back quote is also a problem, so it should be like this.

SELECT TahunMasuk, 
  COUNT(CASE WHEN `JenisKelamin`='Perempuan' THEN 1 END) AS `Perempuan`,
  COUNT(CASE WHEN `JenisKelamin`='Laki-laki' THEN 1 END) AS `Laki-laki`
FROM jeniskelaminreal
GROUP BY TahunMasuk;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269513

You are missing the THEN and END keywords. Also, the string constants need to be enclosed in single quotes:

SELECT TahunMasuk, 
       COUNT(CASE WHEN JenisKelamin = 'Perempuan' THEN 1 END) AS `Perempuan`,
       COUNT(CASE WHEN JenisKelamin = 'Laki-laki' THEN 1 END) AS `Laki-laki`
FROM jeniskelaminreal
GROUP BY TahunMasuk;

MySQL (which you appear to be using) has a convenient shorthand:

SELECT TahunMasuk, 
       SUM(CJenisKelamin = 'Perempua') AS Perempuan,
       SUM(JenisKelamin = 'Laki-laki') AS `Laki-laki`
FROM jeniskelaminreal
GROUP BY TahunMasuk;

Upvotes: 1

juergen d
juergen d

Reputation: 204746

A case has the following syntax:

COUNT(CASE WHEN `JenisKelamin`= 'Perempuan' THEN 1 ELSE null END) AS `Perempuan`

or since the else part is null by default:

COUNT(CASE WHEN `JenisKelamin`= 'Perempuan' THEN 1 END) AS `Perempuan`

In MySQL you could also do

SUM(JenisKelamin = 'Perempuan') AS `Perempuan`

Also string are delimited by quotes and not backticks.

Upvotes: 1

Darshan Mehta
Darshan Mehta

Reputation: 30809

You are using backticks instead of double quotes while comparing the value. Also, your CASE statements are missing THEN and END, try the following:

SELECT TahunMasuk, 
  COUNT(CASE WHEN `JenisKelamin`='Perempuan' THEN 1 END) AS 'Perempuan',
  COUNT(CASE WHEN `JenisKelamin`='Laki-laki' THEN 1 END) AS 'Laki-laki'
FROM jeniskelaminreal
GROUP BY TahunMasuk;

Upvotes: 1

Related Questions