1. Home
  2. Forums Index
  3. Server Side / Databases 4:38 am May 21, 2026

Forum Moderators: open

Message Too Old, No Replies

SQL Subqueries

         

oxidetones

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

10+ Year Member



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

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

10+ Year Member



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

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

10+ Year Member



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

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

10+ Year Member



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

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

10+ Year Member



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

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

10+ Year Member



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

whoisgregg

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

WebmasterWorld Senior Member 10+ Year Member



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. :)
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

  1. Home
  2. Forums Index
  3. Server Side / Databases 4:38 am May 21, 2026