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!!

Sep
14

Virtuemart For Joomla Database Model 1.1.2

Today we searched for a Virtuemart DB model and failed, so we created our own :-)

We created this model in Toad Database Designer. We have provided the link to the design file if anyone would like to utilize it, and a link to the PNG of the model. Please provide feedback on any relations that may not be correct, or are missing.

To download the Toad design file CLICK HERE

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