How to use aliases in query for making conditions in mysql
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
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...
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
- A Linux, Apache, MySQL, PHP (LAMP) stack is a combination of open source software that is typically installed together to enable a server to host dynamic […]
- MySQL is an the most popular open-source database management system, in most of hosting company its has been bundle by LAMP(Linux, Apache, MySQL, PHP/Python/Perl) stack. Another alternative […]
- MySQL is the world’s most popular open-source database. Despite its powerful features, MySQL is simple to set up and easy to use. Below are some instructions […]