condense SQL in mySQL

Question

How can I simplify this code in MySQL?

SELECT name,
  MAX(IF(to_days(thedate) - to_days('2009-06-13') = 0, price, '')) AS date1,
  MAX(IF(to_days(thedate) - to_days('2009-06-13') = 1, price, '')) AS date2,
  MAX(IF(to_days(thedate) - to_days('2009-06-13') = 2, price, '')) AS date3,
  MAX(IF(to_days(thedate) - to_days('2009-06-13') = 3, price, '')) AS date4,
  MAX(IF(to_days(thedate) - to_days('2009-06-13') = 4, price, '')) AS date5,
  MAX(IF(to_days(thedate) - to_days('2009-06-13') = 5, price, '')) AS date6,
  MAX(IF(to_days(thedate) - to_days('2009-06-13') = 6, price, '')) AS date7,
AVG(price),SUM(price)
FROM `personals`
WHERE personal_id = '1234'
GROUP BY name

So that the number of dates calculated are dynamic?

Answer 1

You can't change the number of columns dynamically, but you can get separate rows for each date very easily:

SELECT to_days(thedate) - to_days('2009-06-13') as interval,
   max(price) FROM `personals`
WHERE personal_id = '1234'
GROUP BY name, thedate

You need to do a separate query to get the average data:

SELECT name,
   AVG(price),SUM(price)
FROM `personals`
WHERE personal_id = '1234'
GROUP BY name

Answer 2

If I'm understanding you correctly then I don't think you can do what you want: SQL doesn't work that way. If your query is being used from another program and you knew the set of day counts you were interested in then you could build it as a string prior to submission. If you want all possible numbers of days and must have a single SQL query then a UNION would, I suppose, do it (although I'd prefer two queries):

SELECT
  name
, to_days(thedate) - to_days('2009-06-13') AS num_days
, MAX(price) As max_price
, NULL AS avg_price
, NULL AS sum_price
FROM `personals`
WHERE personal_id = '1234'
GROUP BY
  name
, to_days(thedate) - to_days('2009-06-13') 
UNION ALL
SELECT
  name
, NULL
, NULL
, AVG(price)
, SUM(price)
FROM `personals`
WHERE personal_id = '1234'
GROUP BY name

Answer 3

What probably works best is to split this into two queries, one for loading the average price and sum of prices per person:

SELECT
  AVG(price), SUM(price)
FROM `personals`
WHERE personal_id = '1234'
GROUP BY name;

and a second one for the maxima you want to know:

SELECT
  MAX(price)
FROM `personals`
WHERE personal_id = '1234'
GROUP BY name, to_days(thedate) - to_days('2009-06-13');

If you really want the columns all in the same query, use a subquery for the first one (might not be too efficient on large databases)

SELECT
  MAX(price),
  AVG(price),
  SUM(price)
FROM `personals`
LEFT JOIN (
  SELECT
    AVG(price), SUM(price), name
  FROM `personals`
  WHERE personal_id = '1234' -- # this line is optional
  GROUP BY name
) totals
ON totals.name = personals.name
WHERE personal_id = '1234'
GROUP BY name, to_days(thedate) - to_days('2009-06-13');