CODEWITHSUNDEEP
mysql
dpark
dpark

Reputation: 55

MySQL: using aggregate functions within column values

Given a table within a MySQL schema with a format similar to: person_id | creation_date Example data:

p1 | 08-12-1995
p2 | 04-11-2002
p3 | 12-31-2007
...

I am having difficulty understanding the syntax to do the following functionalities: 1. I want to count the number of people based on their creation date For example, I want to create a table that is GROUPED BY the year and will have a supporting column that has the COUNT of all the people that were created within that year. 2. Use an aggregate function that will be able to calculate the delta between two years' values based on the new GROUPED BY table i.e.

 Year | # of people | Delta
 1995 | 20          | 
 1996 | 22          | +10%
 1997 | 18          | -18%
 ...

I'm new to MySQL and the syntax still confuses me. I don't know if creating a new table GROUP'd based on year is the most effective solution to go about the solution and I am unsure how I would calculate the delta in a table into my own.

Upvotes: 1

Views: 26

Answers (2)

Strawberry
Strawberry

Reputation: 33935

You can also do something like the following, but on larger data sets, an answer something like Uueerdo's should be faster.

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
( year INT NOT NULL
, person VARCHAR(12) NOT NULL
, PRIMARY KEY(year,person)
);

INSERT INTO my_table VALUES
(1995,'Adam'),
(1995,'Ben'),
(1995,'Charlie'),
(1995,'Dan'),
(1996,'Edward'),
(1996,'Fred'),
(1996,'Gary'),
(1996,'Henry'),
(1997,'Inigo'),
(1997,'James'),
(1997,'Kevin'),
(1997,'Leonard'),
(1998,'Michael'),
(1998,'Noah'),
(1998,'Oliver'),
(1998,'Patrick'),
(1998,'Quentin'),
(1998,'Robert'),
(1999,'Stephen'),
(1999,'Thomas'),
(1999,'Umberto'),
(2000,'Victor'),
(2000,'Walterr'),
(2001,'Xavier'),
(2002,'Yanick'),
(2003,'Zac');

 SELECT a.*  
      , a.n/b.n * 100 pct_diff 
   FROM 
      ( SELECT x.year, COUNT(*) n FROM my_table x GROUP BY year ) a 
   LEFT 
   JOIN 
      ( SELECT x.year, COUNT(*) n FROM my_table x GROUP BY year ) b 
     ON b.year = a.year - 1;
+------+---+----------+
| year | n | pct_diff |
+------+---+----------+
| 1995 | 4 |     NULL |
| 1996 | 4 | 100.0000 |
| 1997 | 4 | 100.0000 |
| 1998 | 6 | 150.0000 |
| 1999 | 3 |  50.0000 |
| 2000 | 2 |  66.6667 |
| 2001 | 1 |  50.0000 |
| 2002 | 1 | 100.0000 |
| 2003 | 1 | 100.0000 |
+------+---+----------+

Upvotes: 0

Uueerdo
Uueerdo

Reputation: 15941

Something like this should work, but take care to not change the order of the result columns (they, unofficially, evaluate left to right).

SET @lastCount := null;
SELECT creationYear AS `Year`
, IF(@lastCount IS NULL
     , ''
     , CONCAT(ROUND(100*((pCount/@lastCount)-1), 0), '%')
  ) AS `Delta`
, @lastCount := pCount AS `# of people`
FROM (
   SELECT YEAR(creation_date) AS creationYear, COUNT(1) AS pCount
   FROM theTable
   GROUP BY creationYear
   ORDER BY creationYear
) AS subQ

Upvotes: 1

Related Questions