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 Code:
CREATE TABLE `schedule` ( Code:
+------------------------------------------------------------------------+ |
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 |
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
|
All times are GMT -5. The time now is 08:56 PM. |