homepage Welcome to WebmasterWorld Guest from 54.211.180.175
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
SQL Subqueries
oxidetones




msg:4114913
 9:08 am on Apr 13, 2010 (gmt 0)

I am pulling my hair out trying to grab aggregated data from a single DB table.

The table is as follows:


CREATE TABLE IF NOT EXISTS `qt_tickets` (
`ID` int(11) NOT NULL auto_increment,
`by` int(11) NOT NULL,
`subject` varchar(255) NOT NULL,
`ADE` varchar(15) NOT NULL,
`storno` varchar(15) NOT NULL default '0',
`channel` varchar(20) NOT NULL,
`body` text NOT NULL,
`uploads` tinyint(3) NOT NULL default '0',
`created` datetime NOT NULL,
`reviewdate` date default NULL,
`modified` timestamp NOT NULL default CURRENT_TIMESTAMP,
`priority` varchar(45) NOT NULL,
`status` varchar(55) NOT NULL default 'New',
`parent` int(11) NOT NULL default '0',
`DEPARTMENT_ID` int(11) NOT NULL default '0',
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=77 ;


I am trying to get 4 pieces of data, based on calender week. Ive been able to work out how to get the individual data points in individual queries, however not into one query with subqueries.

I need the following:
* Calender Week based on 'created' (week(created) AS CalWeek)
* The amount of German tickets per CalWeek (selectcount(*) from qt_tickets where ADE LIKE 'ADE%'
* The amount of Austrian tickets per CalWeek (selectcount(*) from qt_tickets where ADE LIKE 'TAT%'
* The amount of German cancelled tickets per CalWeek (selectcount(*) from qt_tickets where ADE LIKE 'ADE%' AND storno LIKE 'von%'
* The amount of Austrian cancelled tickets per CalWeek (selectcount(*) from qt_tickets where ADE LIKE 'TAT%' AND storno LIKE 'von%'

This should be summarized by Cal Week (CalWeek / German Tickets / German Cancellations / Austrian Tickets / Austrian Cancellations)

The problem I am facing is that when I nest the queries, the results are duplicated in all rows. This is how far I got:

select week(created) as CalWeek,
(select count(*) from qt_tickets where ADE LIKE 'ADE%') as ALL_German,
(select count(*) from qt_tickets where storno LIKE '%von%' AND ADE LIKE 'ADE%') as Cancel_German,
(select count(*) from qt_tickets where ADE LIKE 'TAT%') as All_Austrian,
(select count(*) from qt_tickets where storno LIKE '%von%' AND ADE LIKE 'TAT%') as Cancel_Austria
from qt_tickets
GROUP BY week(created)


I'd appreciate any tips in the right directions

Adam

 

syber




msg:4114982
 12:15 pm on Apr 13, 2010 (gmt 0)

What you need is a crosstab query rather than subqueries. Since your subqueries can be executed on their own, the answers will be the same for every row. A crosstab query would look like this:

SELECT week(created) as CalWeek,
SUM(CASE WHEN ADE LIKE 'ADE%' THEN 1 ELSE 0 END CASE) as ALL_German,
SUM(CASE WHEN storno LIKE '%von%' AND ADE LIKE 'ADE%' THEN 1 ELSE 0 END CASE) as Cancel_German,
SUM(CASE WHEN ADE LIKE 'TAT%' THEN 1 ELSE 0 END CASE) as ALL_Austrian,
SUM(CASE WHEN storno LIKE '%von%' AND ADE LIKE 'TAT%' THEN 1 ELSE 0 END CASE) as Cancel_Austria,
FROM qt_tickets
GROUP BY week(created)

oxidetones




msg:4114995
 12:38 pm on Apr 13, 2010 (gmt 0)

Hi Syber,

Thanks for the response, looks like Ill need to read up on CASE and crosstab queries, never seen thos before...

will try that out and let you know how it goes

Adam

oxidetones




msg:4115000
 12:45 pm on Apr 13, 2010 (gmt 0)

Hi again,

it worked perfectly after a minor tweak (I needed to remove CASE from the end of each crosstab query).

Thanks so much!

Adam

oxidetones




msg:4116797
 10:26 am on Apr 16, 2010 (gmt 0)

Hi Syber,

Ive run across another problem that I hope you can help me with.... still using CASE, but I get an error when using INNER JOIN.

I have 3 tables, qt_tickets, qt_assigned, qt_users.

qt_tickets has a UID that is in qt_assigned (grab the tickets where a user is assigned). qt_assigned has an ID that relates to an ID on qt_users, (where I grab the users name). If I use select * I get the complete data I need, however using SUM (CASE... ) as in your previous examples throws an error.

Here is what works

SELECT *
from qt_tickets
INNER JOIN qt_assigned ON qt_tickets.ID = qt_assigned.TICKET_ID
INNER JOIN qt_users ON qt_users.ID = qt_assigned.USER_ID
GROUP BY fname


and here is where something isnt quite right

SELECT
SUM(CASE WHEN status = 'Resolved' AND parent = '0' THEN 1 ELSE 0 end) as resolved,
SUM(CASE WHEN status <> 'Resolved' AND parent = '0' THEN 1 ELSE 0 END) as open,
from qt_tickets
INNER JOIN qt_assigned ON qt_tickets.ID = qt_assigned.TICKET_ID
INNER JOIN qt_users ON qt_users.ID = qt_assigned.USER_ID
GROUP BY fname


The error I get is:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from qt_tickets INNER JOIN qt_assigned ON qt_tickets.ID = qt_assigned.TICKET_ID ' at line 4


What confuses me is that the INNER JOINs havent changed, but I wasnt able to find any reference that CASE doesnt work with INNER JOINs.

Any clever Ideas?

Thanks, Adam

oxidetones




msg:4116798
 10:29 am on Apr 16, 2010 (gmt 0)

OH MY GOD! I feel so stupid.... the comma at the end of the second CASE caused the issue....

whoisgregg




msg:4118297
 7:03 pm on Apr 19, 2010 (gmt 0)

Don't feel stupid. We've all been there and made the same mistakes. To this day, some of the worst bugs I run across are simple syntax errors that *I* made.

Glad you got it sorted out. :)

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved