0

I've created table like that:

sim_mcc_mnc   January    February  March  ....
   232-10      1234        4321    5678    (these numbers are number of records)

By naming each column manually:

SELECT 
    sim_mcc_mnc,
    sum(year(time_utc)=2013 AND month(time_utc)=1) as 'January 2013',
    sum(year(time_utc)=2013 AND month(time_utc)=2) as 'February 2013',
    sum(year(time_utc)=2013 AND month(time_utc)=3) as 'March 2013',
    sum(year(time_utc)=2013 AND month(time_utc)=4) as 'April 2013',
    ...
    ...
    ...

FROM
    table

where
    sim_mcc_mnc like '232-%' OR
    sim_mcc_mnc is null
GROUP BY 
    sim_mcc_mnc
;

My question is. Is there any possibility to name these columns automatically? I've tried to use CONCAT, but it seems to me that you can't use any function after AS.

Is there any other possibility?

mjsqu
  • 5,151
  • 1
  • 17
  • 21
Martin Horák
  • 61
  • 10
  • 2
    @jarlh The real answer is: yes, this is possible, but you have to use a "stored procedure" for this. – arkascha Nov 10 '15 at 10:02
  • Just output as rows, and handle the transformation in your presentation layer (e.g. a bit of PHP) – Strawberry Nov 10 '15 at 10:19
  • This is called a "pivot table" and it has numerous answers on Stack Overflow already, e.g. http://stackoverflow.com/q/7674786/521799 – Lukas Eder Nov 10 '15 at 10:23

1 Answers1

0

I don't know what you are trying to do with the following line...

sum(year(time_utc)=2013 AND month(time_utc)=1) as 'January 2013',

This will return the number of rows which match, not the sum of the values...

To answer your question, you have two possibilities:

  • using a stored procedure, which let you generate also the whole query
  • renaming the column names in the application part, which is the easiest way.
Adam
  • 17,838
  • 32
  • 54
  • I want to find number of records for specific month. The line should also look like this: sum(case when Month(time_utc) = 1 then 1 else 0 end) as January, – Martin Horák Nov 10 '15 at 11:18
  • I am beginning with MySQL, so I am not sure yet where is the border between MySQL and application part, because I find a lot of tasks easier to do afterwards in python than in MySQL. Especially these final data formatting in more human friendly form – Martin Horák Nov 10 '15 at 11:22