2. PHPRunner General questions
  3. [SOLVED]  MYSQL subquery problem
This topic is locked
[SOLVED]

 MYSQL subquery problem

 		12/13/2013 12:30:45
PHPRunner General questions
P
 prattster1874 author

Can anyone give me a hint here, this mysql query displays nothing:
SELECT

date(import_date) as 'Import Date',

(SELECT sum(wip) from tbl_wip where department='1' group by date(import_date)) as 'ws1',

(SELECT sum(wip) from tbl_wip where department='2' group by date(import_date)) as 'ws2',

(SELECT sum(wip) from tbl_wip where department='3' group by date(import_date)) as 'ws3'

FROM tbl_wip

group by date(import_date)
but if I do a subquery on its own it works as expected?
Thanks, and sorry if it is a stupid question!! <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=question&id=21744&image=1&table=forumtopics' class='bbc_emoticon' alt=':)' />
Sergey Kornilov admin 12/13/2013

I'm afraid there is not enough info.
What are you trying to achieve using this query? Show us the desired output.
P
 		prattster1874 author 12/13/2013



I'm afraid there is not enough info.
What are you trying to achieve using this query? Show us the desired output.


sorry!!
I am looking for an output like this for a multi series line chart:
importdate ws1 ws2 ws3

12/12/2013 25 16 18

13/12/2013 31 44 33
data is held in a table like:
id,import_date,department,wip
Sergey Kornilov admin 12/13/2013

I don't think this is possible this way. Your subqueries return more than one row of data meaning you cannot use them as a part of SELECT list.
You can probably try to INNER JOIN those subqueries. Sample syntax:

SELECT ws1,ws2,ws3,tbl_wip.import_date from

(SELECT import_date, sum(wip) as ws1 from tbl_wip where department='1' group by date(import_date)) a

inner join (SELECT import_date, sum(wip) as ws2 from tbl_wip where department='2' group by date(import_date)) b

on a.import_date=b.import_date

inner join (SELECT import_date, sum(wip) as ws3 from tbl_wip where department='3' group by date(import_date)) c

on a.import_date=c.import_date

inner join tbl_wip

on a.import_date=tbl_wip.import_date
P
 		prattster1874 author 12/13/2013



I don't think this is possible this way. Your subqueries return more than one row of data meaning you cannot use them as a part of SELECT list.
You can probably try to INNER JOIN those subqueries. Sample syntax:

SELECT ws1,ws2,ws3,tbl_wip.import_date from

(SELECT import_date, sum(wip) as ws1 from tbl_wip where department='1' group by date(import_date)) a

inner join (SELECT import_date, sum(wip) as ws2 from tbl_wip where department='2' group by date(import_date)) b

on a.import_date=b.import_date

inner join (SELECT import_date, sum(wip) as ws3 from tbl_wip where department='3' group by date(import_date)) c

on a.import_date=c.import_date

inner join tbl_wip

on a.import_date=tbl_wip.import_date



Thanks, got it to work - I suspected I would have to join the subqueries, but couldn't get my head around the syntax. You are a life saver (again!!) <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=73298&image=1&table=forumreplies' class='bbc_emoticon' alt=':)' />