LinuxQuestions.org

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

Wim Sturkenboom 01-24-2007 12:42 AM

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`                        |
+------------------------------------------------------------------------+


karpi 01-24-2007 07:16 AM

Hi,

imho this query constructs parts for other queries.

It essentially says
Add <b>,sum(if(service = "</b> and the content of the field <b>service</b> and <b>,1,0)) as `' </b> 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

Wim Sturkenboom 01-24-2007 07:51 AM

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.

graemef 01-24-2007 10:23 PM

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.

Wim Sturkenboom 01-24-2007 10:37 PM

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.

graemef 01-24-2007 11:29 PM

Here is the relevant chapter from the documantation


All times are GMT -5. The time now is 10:45 PM.