MySQL calculate gender percentage

We need to calculate the gender % from user table based on gender column. So lets say, out of 10 users if there are 5 male and 5 female then we can say that male percentage is 50%

Create a table using below query:

CREATE TABLE `users` (
 `name` varchar(255) DEFAULT NULL,
 `gender` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

Insert some records using below query:

insert into users values ('bishwanath', 'male'), ('robin', 'male'), ('riya', 'female'), ('joe', 'male');

Solution1:

select ((sum(case when gender = 'male' then 1 else 0 end)/count(*))*100) as male_percentage from users limit 1;

Solution2:

select (((select count(*) from users where gender = 'male')/count(*)) * 100) as male_percentage from users limit 1;

There could be more ways of doing it. This is just for reference.

My MySQL version : 10.0.29-MariaDB-0ubuntu0.16.04.1

Advertisements

Using MySQL Utilities Workbench Script mysqldbcompare To Compare Two Databases In Replication

Scripting MySQL

In my last two posts, I wrote about setting up replication with MySQL 5.6 using Global Transaction Identifiers. Even when I set up replication “the old-fashioned way“, one thought always enters my mind – did all of the data copy over to the slave? And, even after the master/slave has been running for a while, I am always wondering if the data in the slave matches the master. Or did the change that I made to that table make it over to the slave? It is probably more of a case of paranoia on my part, as MySQL replication is very reliable and works really well.

A few months ago, I started writing about the MySQL Utilities. If you haven’t heard about the MySQL Utilities:

“MySQL Utilities is a package of utilities that are used for maintenance and administration of MySQL servers. These utilities encapsulate a set…

View original post 1,578 more words

How to Import/Export databases in MySQL from command-line

Recently i worked with MySQL import/export databases and i have found some useful commands below:

Export:

  • All databases into sql file using mysqldump tool
mysqldump -u [username] -p --all-databases > /home/username/filename.sql

In case you want to compress at the same time, run below command

mysqldump -u [username] -p --all-databases | gzip > /home/username/filename.sql.gz
  • Single database into sql file using mysqldump tool
mysqldump -u [username] -p [dbname] > /home/username/filename.sql

In case you want to compress at the same time, run below command

mysqldump -u [username] -p [dbname] | gzip > /home/username/filename.sql.gz
  • Single database table into sql file using mysqldump tool
mysqldump -u [username] -p [dbname] [table_name] > /home/username/filename.sql

In case you want to compress at the same time, run below command

mysqldump -u [username] -p [dbname] [table_name] | gzip > /home/username/filename.sql.gz

Import:

  • Database from sql dump
 mysql -u [username] -p [dbname] < /home/username/filename.sql 

or

 mysql> source /home/username/all_databases_export.sql 
  • Single database from the dump(if it contains multiple databases):
 mysql -u [username] -p --one-database [dbname] < /home/username/all_databases_export.sql 

NOTE :

  • While importing some large database i experience below error something as
 ERROR 1153 (08S01) at line 96: Got a packet bigger than 'max_allowed_packet' bytes 

In this case i just increase the value of –max_allowed_packet(Default=16M) as below command

 mysql -u [username] -p --max_allowed_packet=256M [dbname] < /home/username/filename.sql 
  • Better using absolute paths.