Help with MySQL Group function (I think)
Question posted by: luttkens
(Newbie)
on
August 25th, 2008 12:44 PM
I have a table salary, and when I select it, it looks like this
- Name Year Income
-
-------------------------------
-
James Smith 2008 33000
-
James Smith 2007 32000
-
James Smith 2006 31300
-
James Smith 2005 30100
-
James Smith 2004 28000
-
Oliver M. 2008 19700
-
Oliver M. 2006 18000
-
Oliver M. 2005 16000
How can I make the result look like this instead? I guess there some kind of group function included in the answer...
- Name 2008 2007 2006 2005 2004
-
---------------------------------------------
-
James Smith 33000 32000 31300 30100 28000
-
Oliver M. 19700 null 18000 16000 null
4
Answers Posted
You are asking MySQL to transform rows into columns. I suppose you could do this with a query but it would be a little complicated, and probably needs to know in advance that the columns you wish to show are for the 5 years that you are showing. In other words, it would maybe not be generalized to another set of years.
Usually, to do a presentation like this, you would take the results out of MySQL in a table format like what you have now, and use a scripting or other language to place the results into the column form that you want.
What may help you here is the group_concat function with a group by clause.
It's a fairly complicated query, but here is how you would do it.
Simply Replace `helper` with the name of your table.
- SET @y1 = YEAR(CURDATE());
-
SET @y2 = (YEAR(CURDATE()) - (1));
-
SET @y3 = (YEAR(CURDATE()) - (2));
-
SET @y4 = (YEAR(CURDATE()) - (3));
-
SET @y5 = (YEAR(CURDATE()) - (4));
-
-
CREATE TEMPORARY TABLE `temporary`
-
(
-
`name` VARCHAR(255) NOT NULL,
-
`1` BIGINT(12) UNSIGNED,
-
`2` BIGINT(12) UNSIGNED,
-
`3` BIGINT(12) UNSIGNED,
-
`4` BIGINT(12) UNSIGNED,
-
`5` BIGINT(12) UNSIGNED
-
)
-
ENGINE = MEMORY;
-
-
INSERT INTO `temporary`
-
(
-
`name`
-
)
-
SELECT DISTINCT `name`
-
FROM `helper`;
-
-
UPDATE `temporary`
-
SET
-
`temporary`.`1` =
-
(
-
SELECT `helper`.`income`
-
FROM `helper`
-
WHERE
-
`helper`.`year` = @y1
-
AND `helper`.`name` = `temporary`.`name`
-
),
-
`temporary`.`2` =
-
(
-
SELECT `helper`.`income`
-
FROM `helper`
-
WHERE
-
`helper`.`year` = @y2
-
AND `helper`.`name` = `temporary`.`name`
-
),
-
`temporary`.`3` =
-
(
-
SELECT `helper`.`income`
-
FROM `helper`
-
WHERE
-
`helper`.`year` = @y3
-
AND `helper`.`name` = `temporary`.`name`
-
),
-
`temporary`.`4` =
-
(
-
SELECT `helper`.`income`
-
FROM `helper`
-
WHERE
-
`helper`.`year` = @y4
-
AND `helper`.`name` = `temporary`.`name`
-
),
-
`temporary`.`5` =
-
(
-
SELECT `helper`.`income`
-
FROM `helper`
-
WHERE
-
`helper`.`year` = @y5
-
AND `helper`.`name` = `temporary`.`name`
-
);
-
-
SELECT * FROM `temporary`;
Please note that this query only selects results from the past 5 years.
To do more then that, you'd have to adapt it a bit.
I have no clue to make it into a dynamic form as of right now.
-You'd probably need some advanced loops to be used.
The result table is:
- name 1 2 3 4 5
-
-----------------------------------------
-
James Smith 33000 32000 31300 30100 28000
-
Oliver M. 19700 NULL 18000 16000 NULL
This can be done with MySQL (like shown in the previous post), but I wouldn't advice it.
You would be far better of using some scripting language or some other API to format the data.
MySQL is a database server, meant to store data, not re-arrange it in pretty formations. That is the job of an API.
How would you be executing this query?
Is this meant to be a part of some application?
Would it not be possible to do this using the language in which that application is written?
Quote:
Originally Posted by Atli
This can be done with MySQL (like shown in the previous post), but I wouldn't advice it.
You would be far better of using some scripting language or some other API to format the data.
MySQL is a database server, meant to store data, not re-arrange it in pretty formations. That is the job of an API.
How would you be executing this query?
Is this meant to be a part of some application?
Would it not be possible to do this using the language in which that application is written?
Thanks for your answers!
Well I need it to be dynamic so I used the group_concat function and combined it with a PHP-script.
It works very nice!
|
|
|
What is Bytes?
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 197,039 network members.
|
