Combining 3 Select Statements To Output 1 Table

I have three queries with results. Query 1: SELECT DISTINCT employeeid, work.clientid, ROUND ((CAST (AVG(current_lawn_price) AS numeric) / CAST (AVG((((EXTRACT(HOUR FROM job_finish

Solution 1:

The immediate cause for the error is, quoting the manual:

(ORDER BY and LIMIT can be attached to a subexpression if it is enclosed in parentheses. Without parentheses, these clauses will be taken to apply to the result of the UNION, not to its right-hand input expression.)

Bold emphasis mine.

So, as suggested by @wilx, enclosing each SELECT in parentheses would fix that.

But there is more.

Merge into single query

SELECT employeeid, work.clientid    -- no DISTINCT
     , ROUND ((AVG(current_lawn_price)
             /AVG(extract(epoch FROM job_finish - job_start)))::numeric/79, 2) AS under_over_1
FROM   work
JOIN   timesheet USING (date_linkid)
JOIN   client    USING (clientid)
WHERE (employeeid IN (1, 2) OR
       employeeid =3AND workid <557AND workid >188)
GROUPBY employeeid, clientid 
ORDERBYCASE employeeid
            WHEN1THEN1WHEN2THEN3WHEN3THEN2END
        , clientid;

Merge the 3 SELECT queries.

Remove the redundant DISTINCT. That's an expensive no-op after GROUP BY.

Instead of extracting hours and minutes from both begin and end timestamps etc., compute the interval by plain subtraction (works with timestamp or time values alike) and extract the epoch from it. Gives you the number of seconds. Divide by 60 and you got the number of minutes much faster. 79 being the result of 60 * 1.31666666666667, accordingly.

The manual about extracting epoch:

For timestamp with time zone values, the number of seconds since 1970-01-01 00:00:00 UTC (can be negative); for date and timestamp values, the number of seconds since 1970-01-01 00:00:00 local time; for interval values, the total number of seconds in the interval

Bold emphasis mine.

Since this removes UNION ALL, the parentheses mentioned at the top are not needed any more.

The CASE expression makes up for the mixed order in employeeid, like sticky bit provided.

If queries cannot be merged

If, for some reason, you can't or won't merge the three original SELECT queries, do this instead:

(  -- parentheses requiredSELECT employeeid, work.clientid    -- no DISTINCT !
     , ROUND ((AVG(current_lawn_price)
             /AVG(extract(epoch FROM job_finish - job_start)))::numeric/79, 2) AS under_over_1
FROM   work
JOIN   timesheet USING (date_linkid)
JOIN   client    USING (clientid)
WHERE  employeeid =1AND    workid <557AND    workid >188GROUPBY clientid  -- no need to GROUP BY employeeid while filtering single valueORDERBY clientid
)

UNIONALL
(
SELECT employeeid, work.clientid
     , ROUND ((AVG(current_lawn_price)
             /AVG(extract(epoch FROM job_finish - job_start)))::numeric/79, 2) AS under_over_1
FROM   work
JOIN   timesheet USING (date_linkid)
JOIN   client    USING (clientid)
WHERE  employeeid =3GROUPBY clientid
ORDERBY clientid
)

UNIONALL
(
SELECT employeeid, work.clientid
     , ROUND ((AVG(current_lawn_price)
             /AVG(extract(epoch FROM job_finish - job_start)))::numeric/79, 2) AS under_over_1
FROM   work
JOIN   timesheet USING (date_linkid)
JOIN   client    USING (clientid)
WHERE  employeeid =2GROUPBY clientid
ORDERBY clientid
);
-- no outer ORDER BY required

Keep ORDER BY per SELECT and add parentheses to fix the syntax. UNION ALL (as opposed to UNION) simply appends results preserving the order of individual SELECTs. This should be cheaper than ordering the whole set afterUNION ALL. And you did want to keep using queries individual SELECT queries "as is" ...

Most of the other advice above applies accordingly.

Aside: make it a habit to use table aliases and table-qualify all columns in queries joining multiple tables. Much more robust against later changes and easier to read / debug.

Solution 2:

Remove all the ORDER BYs. Then use the resulting query as a derived table. To retain the order you presumably want, you can then use a CASE expression to map the employeeid to and integer determining the order:

SELECT x.employeeid,
       x.clientid,
       x.under_over_1
       FROM (<your UNIONALL query without the ORDER BYs>) x
       ORDERBYCASE x.employeeid
                  WHEN1THEN1WHEN2THEN3WHEN3THEN2END,
                x.clientid;