Oct
07

MySQL Renumbering Ordered Rows In An Update Statement

Today I searched How To Reorder Column Numbering In MySQL. This can be accomplished by using MySQL variables. We were able to use this solution to reorder a column that had gaps in the numbering after we had run a delete in the DB.

Below is an example of how to do this through a select.

SET @line = 0;

SELECT @line := @line + 1, some_field FROM my_table;

Here is how to accomplish an update

SET @line = 0;

update mytable set myordercolum = @line := @line + 1 order by myordercol;

Don’t forget to adding in your where or proper field to order by for the correct numbering if needed!!

Feb
22

Sql Get Age From Date of Birth

Today I searched mysql get age from date of birth. I was looking get some demographic data from my database’s user table. I found a good starting query from java2s.com which returns each users age in a table called bird.

SELECT name, birth, CURDATE(),
(YEAR(CURDATE())-YEAR(birth))
- (RIGHT(CURDATE(),5)

I modified the query to get the average age.


SELECT avg((YEAR(CURDATE())-YEAR(birthday)) - (RIGHT(CURDATE(),5))

I also wanted to see the average age for each sex.


SELECT sex, avg((YEAR(CURDATE())-YEAR(birthday)) - (RIGHT(CURDATE(),5))

Then i wanted to see the percentage of male and female in the database.


SELECT sex, (100 * count(sex)/(
                    select count(sex) from user_info
           )) as percentSex
FROM user_info
group by sex



Also i wanted to see the percentages for a certain age group


// XX replace with age range you are looking for
SELECT (100 * count(birthday)/(
                   select count(birthday) from user_info
           )) as percentage
FROM user_info
where (YEAR(CURDATE())-YEAR(birthday)) - (RIGHT(CURDATE(),5))

I wanted to see what percentages for male and female were for a certain age group.


// XX replace with age range you are looking for
SELECT sex, (100 * count(birthday)/(
           select count(birthday) from user_info where (YEAR(CURDATE())-YEAR(birthday)) - (RIGHT(CURDATE(),5))

This data will be extremely helpful in my marketing and I was surprised by the results I found.
Go back to top