LinuxQuestions.org - [SOLVED] MySQL query; please explain this one

- Programming (https://www.linuxquestions.org/questions/programming-9/)

- - MySQL query; please explain this one (https://www.linuxquestions.org/questions/programming-9/mysql-query%3B-please-explain-this-one-522252/)

MySQL query; please explain this one

Not sure where this belongs. If it's not programming related, can a moderator please move it to the appropriate forum.

I found a MySQL query on the internet that exactly does what I need for a crosstab query.

I like to know how I must read it so I understand the working of it. I don't understand the bold part.

Code:

select distinct concat(',sum(if(service = "',service,'",1,0)) as `',service,'`') from schedule

The 'schedule' table

Code:

 CREATE TABLE `schedule` (

  `PK_sched` bigint(20) unsigned NOT NULL auto_increment,

  `datim` datetime default NULL,

  `service` char(64) default NULL,

  PRIMARY KEY  (`PK_sched`)

) TYPE=MyISAM

This is the output of the above query

Code:

+------------------------------------------------------------------------+

| concat(',sum(if(service = "',service,'",1,0)) as `',service,'`')      |

+------------------------------------------------------------------------+

| ,sum(if(service = "espn",1,0)) as `espn`                              |

| ,sum(if(service = "national geographic",1,0)) as `national geographic` |

| ,sum(if(service = "history",1,0)) as `history`                        |

+------------------------------------------------------------------------+

Hi,

imho this query constructs parts for other queries.

It essentially says
Add ,sum(if(service = " and the content of the field service and ,1,0)) as `' giving the content of service as fieldname.

I think
The result would be a crosstable-Query (MS-Access terminology) resulting from the following base query

Select service,count(service) from schedule group by service

HTH

It indeed constructs part for another query. The use of sum is a bit strange to me in this context (although I have seen it more often), but the use of back-ticks is absolutely new to me.

In combination with the next query you indeed get a croostab query.

PS if you want to make text bold, use square brackets instead of smaller_than and greater_than.

The backtick is used to force MySQL to treat the result as a literal or identifier and not a reserved word. So if in your table you had an entry select, without the backtick it would generate an error.

Thanks graemef,

so that allows me to use fieldnames that have a meaning in MySQL as well; stumbled over that problem before and that is now solved (if I understand correctly).
Will try it the next time that I need it.

Here is the relevant chapter from the documantation