Dan Shapiro wrote:
> My data looks as follows
> ----------------------------
> | Team | Year | Ind1 | Ind2 |
> ----------------------------
> | 1 | 2004 | Y | Y |
> | 1 | 2005 | Y | N |
> | 2 | 2004 | N | Y |
> | 2 | 2005 | N | N |
> ----------------------------
>
> I would like to somehow join these together to produce something that shows each Team and how
> many instances exist for a team with an indicator set to 'Y', aka:
>
> ------------------------------------
> | Team | Count(Ind1) | Count(Ind2) |
> ------------------------------------
> | 1 | 2 | 1 |
> | 2 | 0 | 1 |
> ------------------------------------
USE test;
DROP TABLE IF EXISTS foo;
CREATE TABLE foo (team INT, year YEAR, ind1 ENUM('Y','N'), ind2 ENUM('Y', 'N'));
INSERT INTO foo VALUES
(1, 2004, 'Y', 'Y'),
(1, 2005, 'Y', 'N'),
(2, 2004, 'N', 'Y'),
(2, 2005, 'N', 'N');
SELECT
team,
SUM(IF(ind1 = 'Y', 1, 0)) AS CInd1,
SUM(IF(ind2 = 'Y', 1, 0)) AS CInd2
FROM foo
GROUP BY team;
This is in fact a (very simple) crosstab query. More on crosstab queries can be found here:
http://dev.mysql.com/tech-resources/articles/wizard/index.html
--
felix