MySQL Derived Table Issue

Question

I'd like to be able to output the following fields from the query below:

AddedById,AddedByName,HoursWorked,CurrentYearlyFlexiAvailable

However where I have WHERE addedby=1, I'd like to replace this with the field name AddedById as I do not want to hard code this value as the overall query should and will return more than one person, I want to get this value from the rpt_timesheet_data view, which is there. The CurrentYearlyFlexiAvailable field should be telling me how much time they have left for the current year to date by doing the calculation between the SELECT SUM(ttl) and from the FROM (SELECT SUM(worked)-420 as ttl

SELECT AddedById,AddedByName,SUM(HoursWorked) AS HoursWorked 
,(SELECT SUM(ttl) - (
                SELECT SUM(worked) 
                FROM vwtimesheet
                WHERE addedby=AddedById 
                AND entrydate BETWEEN '2017-01-01' AND '2017-04-13' 
                AND activityid=3192 
                GROUP BY addedby ) AS flexihours 

        FROM (
            SELECT SUM(worked)-420 AS ttl 
            FROM vwtimesheet 
            WHERE addedby=1 <!--HERE IS THE ISSUE
            AND entrydate BETWEEN '2017-01-01' AND '2017-04-13' 
            AND projectid<>113 AND activityid<>3192 
            GROUP BY entrydate 
            HAVING SUM(worked)>420
        ) AS s) AS CurrentYearlyFlexiAvailable
FROM rpt_timesheet_data
WHERE entrydate BETWEEN '2017-04-02' AND '2017-04-13 23:59:59' 
AND ActivityId=3192 
GROUP BY AddedById,AddedByName 
ORDER BY AddedByName

but I keep getting:

Error Code: 1054. Unknown column 'AddedById' in 'where clause'

Just in that one location. I've tried various queries to sort this, but just cannot figure it out. Sorry not to good at explaining this, can see it in my head what I want to do...

Here is a query that does something very similar in that it returns the results for a single user, where as the one above is meant to loop through all users and give me the results:-

    SELECT addedbyname, SUM(ttl) - 
    (SELECT SUM(worked) 
        FROM vwtimesheet 
        WHERE addedby=1 
        AND entrydate BETWEEN '2017-01-01' AND '2017-04-13' 
        AND activityid=3192 
        GROUP BY addedby ) AS CurrentYearlyFlexiAvailable 
    ,(SELECT SUM(worked) FROM vwtimesheet 
        WHERE addedby=1 
        AND entrydate BETWEEN '2017-01-01' AND '2017-04-13' 
        AND activityid=3192 
        GROUP BY addedby ) AS flexiused 
    ,(SELECT sum(worked) FROM vwtimesheet 
        WHERE addedby=1 
        AND entrydate BETWEEN DATE_FORMAT(NOW() ,'%Y-%m-01') AND curdate() 
        AND activityid=3192
        GROUP BY addedby ) as fleximonthused 
FROM ( SELECT entrydate,addedbyname,SUM(worked)-420 AS ttl FROM vwtimesheet 
    WHERE addedby=1 
    AND entrydate BETWEEN '2017-01-01' AND '2017-04-13' 
    AND projectid<>113 
    AND activityid<>3192 
    GROUP BY entrydate,addedbyname
    HAVING SUM(worked)>420 
) AS s

Perhaps you should ask another question. Or at least seriously edit this one. Provide sample data, desired results, and describe what logic you are tryng to implement. — Gordon Linoff, Apr 13 '17 at 13:57
In your internal queries you need to include the fields that you are applying `GROUP BY` to. I shall try to correct your code and post an Answer shortly. — toonice, Apr 13 '17 at 14:01
You may find it helpful to read this https://meta.stackoverflow.com/questions/271055/tips-for-asking-a-good-structured-query-language-sql-question/271056#271056 — O. Jones, Apr 13 '17 at 14:09

toonice · Accepted Answer · 2017-04-25T01:36:30.130

0

Please try the following...

SELECT AddedById,
       AddedByName,
       SUM( HoursWorked ) AS HoursWorked,
       SUM( ttl ) - sumWorked AS CurrentYearlyFlexiAvailable
FROM ( SELECT AddedById AS AddedById,
              AddedByName AS AddedByName
       FROM rpt_timesheet_data
       GROUP BY AddedById
     ) AS AddedByFinder
JOIN ( SELECT addedby AS addedby,
              entrydate AS entrydate,
              SUM( worked ) - 420 AS ttl
       FROM vwtimesheet
       WHERE entrydate BETWEEN '2017-01-01' AND '2017-04-13'
         AND projectid <> 113
         AND activityid <> 3192
       GROUP BY addedby,
                entrydate
       HAVING SUM( worked ) > 420
     ) AS ttlFinder ON AddedByFinder.AddedById = ttlFinder.addedby
JOIN ( SELECT addedby AS addedby,
              SUM( worked ) AS sumWorked
       FROM vwtimesheet
       WHERE entrydate BETWEEN '2017-01-01' AND '2017-04-13'
         AND activityid = 3192
       GROUP BY addedby
     ) sumWorkedFinder ON AddedByFinder.AddedById = sumWorkedFinder.addedby
WHERE entrydate BETWEEN '2017-04-02' AND '2017-04-13 23:59:59'
  AND ActivityId = 3192
GROUP BY AddedById,
         AddedByName 
ORDER BY AddedByName;

(Explanation to follow...)

If you have any questions or comments, then please feel free to post a Comment accordingly.

edited Apr 25 '17 at 01:36

answered Apr 13 '17 at 14:09

toonice

2,211
1
13
20

Oops - please hold. – toonice Apr 13 '17 at 14:09
I have updated my Answer. Please get back to me with how it goes. – toonice Apr 13 '17 at 15:08
Thanks for that @toonice, just what I needed. – Martin Apr 24 '17 at 13:26
I will have that explanation up soon - I got a little sidetracked there. – toonice Apr 24 '17 at 14:00
May have spoke to soon, for some reason the **ttl** value returned is actually being doubled, which throws the **SUM( ttl ) - sumWorked AS CurrentYearlyFlexiAvailable** calculation out. If I just run the first join query on it's own the list of values I get are all correct but it seems to be when **ttl** is then used outside of the join – Martin Apr 24 '17 at 14:18
I shall look into it. – toonice Apr 24 '17 at 15:09
Does each AddedByID occur twice within rpt_timesheet_data within the period specified? – toonice Apr 24 '17 at 17:24
I suspect that each value of `AddedByID` can have more than one row in `rpt_timesheet_data` that meets our `WHERE` criteria. I have altered my Answer to eliminate this problem. – toonice Apr 25 '17 at 01:42
The AddedByID can occur many times but the final GROUP BY should take care of that I would have thought. Have tried the amendments you've provided and just get more errors now, like Unknown Column HoursWorked. Think the simple answer would be just to SUM(ttl / 2) - sumWorked, may not be pretty but effective and have tried different sets of data and getting what I'm expecting. Have taken up plenty of your time on this, which is much appreciated. – Martin Apr 25 '17 at 07:41
In you Question you ask for `AddedById, AddedByName, HoursWorked, CurrentYearlyFlexiAvailable`. Is `HoursWorked` the same as `sumWorked` in my answer, or is it the same as `ttl` before `420` is subtracted, or is it something else? – toonice Apr 25 '17 at 07:59
The statement works by joining the results of the three queries / tables together on their shared common value `AddedByID`. Each row from `rpt_timesheet_data` will have each row from the two queries added onto it where they share the common value. This means that the rows from the queries will be added onto `rpt_timesheet_data` multiple times if multiple instances of the shared value exist in `rpt_timesheet_data`. `GROUP BY` and `SUM()` will only take effect after this joining has occurred. My revised Answer performs the `GROUP BY` first to avoid this phenomenon. – toonice Apr 25 '17 at 08:09
Does `HoursWorked` come from `rpt_timesheet_data`? – toonice Apr 25 '17 at 08:10
The first join, is used to get **Hours over the 7 hours for current year**, the second join is used to get **Flexi time used for current month** and the final where is used to get **To make sure we get the any flexi time used for the current year** which is where HoursWorked comes from – Martin Apr 25 '17 at 08:15

MySQL Derived Table Issue

1 Answers1