LinuxQuestions.org
Review your favorite Linux distribution.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices

Reply
 
Search this Thread
Old 01-24-2007, 12:42 AM   #1
Wim Sturkenboom
Senior Member
 
Registered: Jan 2005
Location: Roodepoort, South Africa
Distribution: Slackware 10.1/10.2/12, Ubuntu 12.04, Crunchbang Statler
Posts: 3,786

Rep: Reputation: 282Reputation: 282Reputation: 282
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`                         |
+------------------------------------------------------------------------+

Last edited by Wim Sturkenboom; 01-24-2007 at 12:46 AM.
 
Old 01-24-2007, 07:16 AM   #2
karpi
Member
 
Registered: Oct 2005
Location: Germany
Distribution: Suse
Posts: 133

Rep: Reputation: 15
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
 
Old 01-24-2007, 07:51 AM   #3
Wim Sturkenboom
Senior Member
 
Registered: Jan 2005
Location: Roodepoort, South Africa
Distribution: Slackware 10.1/10.2/12, Ubuntu 12.04, Crunchbang Statler
Posts: 3,786

Original Poster
Rep: Reputation: 282Reputation: 282Reputation: 282
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.
 
Old 01-24-2007, 10:23 PM   #4
graemef
Senior Member
 
Registered: Nov 2005
Location: Hanoi
Distribution: Fedora 13, Ubuntu 10.04
Posts: 2,379

Rep: Reputation: 148Reputation: 148
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.

Last edited by graemef; 01-24-2007 at 10:25 PM.
 
Old 01-24-2007, 10:37 PM   #5
Wim Sturkenboom
Senior Member
 
Registered: Jan 2005
Location: Roodepoort, South Africa
Distribution: Slackware 10.1/10.2/12, Ubuntu 12.04, Crunchbang Statler
Posts: 3,786

Original Poster
Rep: Reputation: 282Reputation: 282Reputation: 282
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.
 
Old 01-24-2007, 11:29 PM   #6
graemef
Senior Member
 
Registered: Nov 2005
Location: Hanoi
Distribution: Fedora 13, Ubuntu 10.04
Posts: 2,379

Rep: Reputation: 148Reputation: 148
Here is the relevant chapter from the documantation
 
  


Reply

Tags
mysql


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
help with mysql query: return nth rows in query hawarden Programming 2 07-31-2006 06:36 PM
mysql query sailu_mvn Linux - Software 1 04-06-2006 05:27 AM
Need mysql query help DropHit Linux - Software 7 04-01-2005 11:32 AM
Mysql Error: Lost Connection to Mysql during query ramnath Programming 5 11-18-2003 12:27 PM
mysql query ? shaahul Linux - Software 1 09-06-2003 03:35 AM


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

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration