CODEWITHSUNDEEP
sqlsqlite
JK36
JK36

Reputation: 853

sqlite return subquery multi column

I have a sqlite subquery with the following query which is calculating the hours and labour_rate. The only issue I now have is can I get the two columns from my subquery to output in the main query. I've tried to layout the query according to some of the web tutorials but need that little bit of help to get me over the finish line as I keep getting a syntax error

  SELECT c.customerID, c.customer, sum( ifnull(il.line_price, 0 ) )/10000 AS net,  
  FROM customer AS c
  LEFT JOIN invoice AS i
  ON c.customerID = i.customerID
  LEFT JOIN invoice_line AS il
  ON i.invoiceID = il.invoiceID
  (SELECT sum(( ifnull(tl.mon,0) + ifnull(tl.tues,0) + ifnull(tl.wed,0) + ifnull(tl.thurs,0) + ifnull(tl.fri,0) + ifnull(tl.sat,0) + ifnull(tl.sun,0) ) * s.cost_rate)/10000 AS labour_rate, 
  sum(( ifnull(tl.mon,0) + ifnull(tl.tues,0) + ifnull(tl.wed,0) + ifnull(tl.thurs,0) + ifnull(tl.fri,0) + ifnull(tl.sat,0) + ifnull(tl.sun,0) ))/10000 AS
  FROM timesheet_line AS tl
  LEFT JOIN timesheet AS t
  ON tl.timesheetID = t.timesheetID
  LEFT JOIN staff AS s
  ON t.staffID = s.staffID
  WHERE (c.customerID = tl.customerID) AND (t.date BETWEEN '2014-03-01' AND '2015-12-01')
  GROUP BY tl.customerID) AS time1
  WHERE (i.date BETWEEN '2014-03-01' AND '2015-12-01') AND (time1.customerID = tl.customerID)
  GROUP BY c.customerID
  ORDER BY c.customer ASC

Upvotes: 2

Views: 2786

Answers (1)

Kidiskidvogingogin
Kidiskidvogingogin

Reputation: 296

There are a few syntax errors in here. The first (and possibly most important) is you have to JOIN your subquery to the rest of the result set. So after 

LEFT JOIN invoice_line AS il
  ON i.invoiceID = il.invoiceID

you will need to add another JOIN statement: 

LEFT JOIN 
    (SELECT 
         SUM(...) AS labour_rate, 
         SUM(...) AS hours,
         tl.customerID
     FROM
     ...) AS time1 
 ON <your join condition>

You will have to select some sort of field in your sub query that you can join back to the the invoice on i.e. tl.customerID. Also, in your subquery you cannot reference a field that is outside of it, so where you have WHERE (c.customerID = tl.customerID) in your subquery it will fail because you are trying to reference c.<fieldname>. It needs to be moved to the ON part of the JOIN clause. Once you get your JOIN working correctly then you can just change your outer-most SELECT to something like 

SELECT c.customerID, c.customer, sum(ifnull(il.line_price,0))/10000 AS net, time1.labour_rate, time1.hours

Here's an example of how I would do it:

SELECT c.customerID, c.customer, sum( ifnull(il.line_price, 0 ) )/10000 AS net, time1.labour_rate, time1.[hours]
FROM customer AS c
    LEFT JOIN invoice AS i
    ON c.customerID = i.customerID
    LEFT JOIN invoice_line AS il
    ON i.invoiceID = il.invoiceID
    LEFT JOIN
        (SELECT 
            sum(( ifnull(tl.mon,0) + ifnull(tl.tues,0) + ifnull(tl.wed,0) + ifnull(tl.thurs,0) + ifnull(tl.fri,0) + ifnull(tl.sat,0) + ifnull(tl.sun,0) ) * s.cost_rate)/10000 AS labour_rate, 
            sum(( ifnull(tl.mon,0) + ifnull(tl.tues,0) + ifnull(tl.wed,0) + ifnull(tl.thurs,0) + ifnull(tl.fri,0) + ifnull(tl.sat,0) + ifnull(tl.sun,0) ))/10000 AS [hours],
            tl.customerID
         FROM timesheet_line AS tl
            LEFT JOIN timesheet AS t
            ON tl.timesheetID = t.timesheetID
            LEFT JOIN staff AS s
            ON t.staffID = s.staffID
        WHERE (t.date BETWEEN '2014-03-01' AND '2015-12-01')
        GROUP BY tl.customerID) AS time1
    ON c.customerID = time1.customerID
WHERE (i.date BETWEEN '2014-03-01' AND '2015-12-01') 
GROUP BY c.customerID
ORDER BY c.customer ASC

Upvotes: 6

Related Questions