CODEWITHSUNDEEP
sqlpivot
Jimi
Jimi

Reputation: 25

Three Aliases in SQL

I have confused to view three condition using AS in SQL, this is my table

id | Fruit
___________
1  | Apel
2  | Orange
3  | Apel
4  | Manggo
___________

This is my first query:

SELECT
COUNT(fruit) AS apelfruit
FROM fruit_table
WHERE fruit = 'Apel'

Output:

apelfruit
_________
    2

But when I do this:

SELECT
COUNT(fruit) AS apelfruit,
COUNT(fruit) AS orangefruit,
COUNT(fruit) AS manggofruit
FROM fruit_table
WHERE fruit = 'Apel'
AND fruit = 'Orange'
AND fruit = 'Manggo'

The output was zero (0):

 apelfruit | orangefruit | manggofruit
 ____________________________________
       0            0            0

I'm little bit confused whats wrong. Anyone can help me I'd appreciate very much. Thank you.

Upvotes: 0

Views: 44

Answers (3)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521914

I prefer using COUNT here, for brevity:

SELECT
    COUNT(CASE WHEN fruit = 'Apel'   THEN 1 END) AS apelfruit,
    COUNT(CASE WHEN fruit = 'Orange' THEN 1 END) AS orangfruit,
    COUNT(CASE WHEN fruit = 'Manggo' THEN 1 END) AS manggofruit
FROM fruit_table;

As @nicomp has pointed out, the limitation to doing a pivot query this way is that the columns which appear in the output are hard-coded, and more code must be added for new conditional counts. But, the only real way around this is to use dynamic SQL.

Upvotes: 1

Guy Louzon
Guy Louzon

Reputation: 1213

The condition is your problem a fruit can't equal to "apel" and to "mango" it should be "apel" or "apel"

Since just counting fruits, will always count all fruits, you also need to count with a condition, in this solution, summing 1 or 0, based on ghe condition:

SELECT
SUM(IF(fruit='Apel', 1,0)) AS apelfruit,
SUM(IF(fruit='Orange', 1,0)) AS orangfruit,
SUM(IF(fruit='Manggo', 1,0)) AS manggofruit
FROM fruit_table WHERE fruit IN ('Apel', 'Orange', 'Manggo')
;

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31991

try below way

SELECT
sum(case when fruit = 'Apel' then 1 else 0 end) AS apelfruit,
sum(case when fruit = 'Orange' then 1 else 0 end) AS orangfruit,
sum(case when fruit = 'Manggo' then 1 else 0 end) AS manggofruit
FROM fruit_table

Upvotes: 1

Related Questions