Geek asks and answers

Combine 2 selection teams

I have 2 select statements and you want to combine them so that they appear in 1 table.

Choose 1:

SELECT
LAST_DAY, SUM(BILLABLE), AVG(BILLABLE)
FROM EMPLOYEES
GROUP BY YEAR(LAST_DAY),WEEK(LAST_DAY) ORDER BY LAST_DAY

Choose 2:

SELECT BILLABLE 
FROM EMPLOYEES 
WHERE NAME LIKE '$lookup%'

The goal is to show the entire amount paid by the employee and the average for the week (first query of choice), and then add a column with the specific paid article of the employee to search for this week.

Both operators work perfectly individually, but it would be more meaningful for the employee if they could easily compare where they are on a weekly basis, compared to the average.

Edit for example:

The EMPLOYEE table looks something like this:

NAME | LAST_DAY | Billable
Bob 05/13/2011 18.5
Mary 05/13/2011 12.68
Steve 11/13/2011 15.2
Bob 06/06/2011 14.1
Mary 06/06/2011 11.17
Steve 06/06/2011 23.62

, :
$lookup == ""

LAST_DAY   | ALL_BILLABLE_TOTAL | ALL_BILLABLE_AVG | EMPLOYEE_BILLABLE_TOTAL
05/13/2011     46.38                15.46                 18.5
05/06/2011     48.89                16.29                 14.1
+3
1

" " , kludge, . , , "" - , - . 3NF, , , . , , , :

SELECT
    SQ.last_day,
    SQ.all_billable_total,
    SQ.avg_billable_total,
    LU.billable AS employee_billable_total
FROM
    (
        SELECT
            E.last_day,
            SUM(E.billable) AS all_billable_total,
            AVG(E.billable) AS avg_billable_total,
        FROM
            Employees
        WHERE
            name NOT LIKE '$lookup%'
        GROUP BY
            E.last_day
    ) SQ
LEFT OUTER JOIN Employees LU ON
    LU.name LIKE '$lookup%' AND
    LU.last_day = SQ.last_day

, , . , - :

SELECT
    E.last_day,
    SUM(CASE WHEN name = @name THEN E.billable ELSE NULL END) AS all_billable_total,
    AVG(CASE WHEN name = @name THEN E.billable ELSE NULL END) AS avg_billable_total,
    SUM(E.billable) AS employee_billable_total
FROM
    Employees E
WHERE
    E.last_day IN (SELECT last_day FROM Employees WHERE name = @name)
GROUP BY
    E.last_day

NULL CASE, 0 .

+2

More articles:


All Articles