How to correctly combine these two queries?-CodePudding

Employees table :

EmpID (Primary Key)	LastName	FirstName

Orders table :

OrderID (Primary Key)	CustID	EmpID	OrderDate	RequiredDate	ShippedDate

I need a query which returns EmpID, LastName, FirstName, total number of orders by employee, and total number of orders shipped late. An order is late if its ShippedDate is 5 days after RequiredDate. I got the late shipments by :

julianday(ShippedDate) - julianday(RequiredDate) >= 5

I thought to make two separate queries, one that takes a total count of the submissions and one for total count of late orders, then UNION them. I'm not getting the result I am looking for:

SELECT Employees.EmpId, Employees.LastName, Employees.FirstName, COUNT(*) as TotalCount, NULL
FROM Orders, Employees
WHERE Orders.EmpID = Employees.EmpID
GROUP BY LastName
UNION
SELECT Employees.EmpId, Employees.LastName, Employees.FirstName, NULL, COUNT(*) as LateCount
FROM Orders, Employees
WHERE Orders.EmpID = Employees.EmpID
AND julianday(ShippedDate) - julianday(RequiredDate) >= 5
GROUP BY LastName

I end up with offset null values on right side of the table :

TotalCount	NULL
17	NULL
NULL	25

etc.

What went wrong with my UNION? Why is the data offset and the column header wrong?
Is there a better approach?

CodePudding user response：

"Is there a better approach?"

JOIN instead of UNION :

SELECT
    Employees.EmpID,
    Employees.LastName,
    Employees.FirstName,
    count(*) AS TotalCount,
    sum(
        julianday(Orders.ShippedDate) - julianday(Orders.RequiredDate) >= 5
    )        AS LateCount
FROM
    Employees
JOIN Orders ON Orders.EmpID = Employees.EmpID
GROUP BY
    Employees.EmpID
ORDER BY
    TotalCount DESC,
    LateCount  DESC