How to use aliases in query for making conditions in mysql


Problem :

I have user table like as follows

user_id    name    gender    age
--------------------------------
1          AAA      Male      45
2          BBB      Female    22
3          CCC      Male      47
.................................
..............................

I want to get total no of users and total no of male users ans percent for male and female users

select count(*) as total , SUM(IF(v.gender = 'Male',1,0)) as totalMale ,
   SUM(IF(v.gender = 'Female',1,0)) as totalFemale , totalMale/total *100 ,
   totalFeMale/total *100 from user;

This query is not working when using aliases to calculate male and female percent. I am getting the error like unknown columns..........

select count(*) as total , SUM(IF(v.gender = 'Male',1,0)) as totalMale ,
SUM(IF(v.gender = 'Female',1,0)) as totalFemale , SUM(IF(v.gender = 'Male',1,0))/count(*) *100 ,SUM(IF(v.gender = 'Female',1,0))/count(*) *100 from user;

But this is working.

But is this i used SUM(IF(v.gender = 'Female',1,0)) 2 times.I think it will degrade the performance.

Can't i use aliases in my situation ?

Thanks in advance...



Solution :

Your query is just fine. You can't use an alias at a select level. Your only option would be to have a derived table but that will indeed degrade performance.

Just one thing to improve the query performance would be to change the Gender column into just a boolean or char column.

Tip: All non-null boolean comparisons in MySQL resolve to 1 (true) and 0 (false), so you could simplify your query this way:

select
    count(*) total,
    SUM(gender = 'Male') totalMale,
    SUM(gender = 'Female') totalFemale,
    SUM(gender = 'Male') / count(*) * 100 percentageMale,
    SUM(gender = 'Female') / count(*) * 100 percentageFemale
from user

Mysql Tutorials

Mysql Howto..

How to Connect with MySQL Database File( .sql ) using Directory Path Like MS Access?

How to get an automatically incremented column value when inserting a row into a table in mysql using java?

With MySQL, how to count members who are in an option and not in an other one?

How to invoke a commonly used MySQL query

How to receive MYSQL warnings (E.g. #1264 Out of Range) from PHP PDO

How to add value in SQL?

How to migrate data from MySQL 5 to Sybase ASE 15.5? [closed]

Set to null a parent record so that children are removed: howto?

How to insert PAST dates into MySQL as Datetime?

How can I delete duplicate join table records in MySQL?

How to display database value inside select tag using PHP and MySQL

How to add data dynamically in a json

JPA Desktop APP and Remote MySQL - How To

Django - how to order a sequence of videos within a topic?

How to delete strings which contain random characters by regex syntax

How to get same effect as like %someword% with match-against in MySQL with using solr etc

How do I select a range of cells based on named start and end cells in VBA?

how to appear form element only if there is no “%” sign in php

How do I convert text entered in form to Title Case Using PHP before submitting to MySQL?

How to get BLOB image file from MYSQL server via JSON efficiently

How to import an excel file in to a MySQL database

mysql query taking too much time for processing. how to solve this issue [closed]

How to access values displayed in a php dropdownlist populated from a mysql database

Django - There was a decent size cascade delete, now how do I fix this?

How to connect 2 databases in php and mysql?

How to avoid race condition if several clients select in MySQL?

How to force foreach to display div without data available from database

How to remove the first characters in a column

How do I convert MySQL unsigned ints to Protocol Buffer uint32s in Java?

mysql, how to join this key id?