Hi. I am working on a way to calculate aggregate values in a table and display the results that are not 0.

Specifically, I have a table called tblParcelAssigned AS PA. When a Parcel is scanned in, it creates a record in PA with the scan in time. It also creates a record for the parcel to be scanned out, with the time (PA.ScannedWhen) as a null value until the parcel is scanned out, at which time PA. ScannedWhen is updated to the scan in time.

I am trying to create a query which when run will alert staff that one of two conditions has taken place:

1. A parcel is scanned in but has not been scanned out.2. A parcel was scanned out but was never scanned in.

In essence, an over and short alert. If the difference between the total in and total out is not 0, then there is a problem.

I am trying to use a GROUP BY as my initial test but am getting errors. Here is the code:

I didn't follwo the reason for joining on the tblOrder table. But from your description, it seems like you want the third query below

-- 1 how many are there in total
SELECT
PA.ParcelID,
COUNT(*) AS TotalPackages
FROM
tblParcelAssigned AS PA
GROUP BY
PA.ParcelID
-- 2 how many have a problem?
SELECT
PA.ParcelID,
COUNT(*) AS TotalProblemPackages
FROM
tblParcelAssigned AS PA
WHERE
CreatedWhen IS NULL OR ScannedWhen IS NULL
OR CreatedWhen > ScannedWhen
GROUP BY
PA.ParcelID
-- 3 break down the problem count
SELECT
PA.ParcelID,
COUNT(*) AS TotalPackages,
SUM(CASE WHEN CreatedWhen IS NOT NULL AND ScannedWhen IS NULL THEN 1 ELSE 0 END) AS NotScannedOut,
SUM(CASE WHEN CreatedWhen IS NULL AND ScannedWhen IS NOT NULL THEN 1 ELSE 0 END) AS NotScannedIn
FROM
tblParcelAssigned AS PA
GROUP BY
PA.ParcelID

The order table was in there because it allows the result to be "clickable", so our CSR can review the order. For example, running query # 1 and clicking on a row would result in this error message: "Cannot edit from this query. First column must contain the primary key field."

The interface I'm stuck with using won't allow me to run consecutive queries. So you sort of have to jump through hoops and embed alot of stuff. Which makes it even crazier!

I guess you get error because of Group BY clause. When you use group by, except aggregate column, all other columns used in select statement should be listed in group by clause. I don't know how z data in your Orders and parcelassigned tables, however I created below script based on my understanding for you. Hope this would help. If you need more help, please list down the data in tables and expected output.

What I was showing you and what shan was showing you are examples. I posted 3 different queries because I was not sure what you will need. You can write one query, joining the tables as necessary. If you need the OrderId as well, modify the 3rd query I had posted to join on the orders table (exactly like you had in your original query) and add the OrderId column in the select list and in the group by clause.

SELECT
O.OrderId,
PA.ParcelID,
COUNT(*) AS TotalPackages,
SUM(CASE WHEN CreatedWhen IS NOT NULL AND ScannedWhen IS NULL THEN 1 ELSE 0 END) AS NotScannedOut,
SUM(CASE WHEN CreatedWhen IS NULL AND ScannedWhen IS NOT NULL THEN 1 ELSE 0 END) AS NotScannedIn
FROM
tblOrder AS O
INNER JOIN tblParcelAssigned AS PA ON O.OrderID = PA.OrderID
GROUP BY
O.OrderId
PA.ParcelID

The way the CASEs were embedded into the aggregate was really cool and is going to help me a lot. Thank you.

Am still not getting the expected results but your illustrations have really given me a good jump start. Am thinking I need to play with the CASE statements, because items that have been both scanned in and scanned out are showing in the results. I think I need to take the total of each parcel ID that were scanned in, then the total that were scanned out, and the differences that are not zero would be the suspected offenders.

Also, COUNT (&) AS 'Total Parcels' always show as 1, maybe because there will always be only one record for each Pa entry?

If I wanted to filter the results by Notscannedin or Notscannedout, can i use the aliases (e. WHERE Notscannedin > 0)?

I don't fully understand the relationships between parcels and orders - whether it is one to one or one to many etc. If for example, one order can have many parcels, and you are trying to get the status of all packages within each orderid, then you would not need to group by parcelid. If you want to get only parcels that have ScannedWhen or CreatedWhen is null, then you would need to add that to the where clause. So one of these (you don't need to run both these queries, I am just showing two possibilities. You probably need one of these or some variation of one of these)

You cannot use aggregate in the WHERE clause, but you can use it in the having clause - see the third query below

SELECT
O.OrderId,
COUNT(*) AS TotalPackages,
SUM(CASE WHEN CreatedWhen IS NOT NULL AND ScannedWhen IS NULL THEN 1 ELSE 0 END) AS NotScannedOut,
SUM(CASE WHEN CreatedWhen IS NULL AND ScannedWhen IS NOT NULL THEN 1 ELSE 0 END) AS NotScannedIn
FROM
tblOrder AS O
INNER JOIN tblParcelAssigned AS PA ON O.OrderID = PA.OrderID
GROUP BY
O.OrderId
SELECT
O.OrderId,
COUNT(*) AS TotalProblemPackages,
SUM(CASE WHEN CreatedWhen IS NOT NULL AND ScannedWhen IS NULL THEN 1 ELSE 0 END) AS NotScannedOut,
SUM(CASE WHEN CreatedWhen IS NULL AND ScannedWhen IS NOT NULL THEN 1 ELSE 0 END) AS NotScannedIn
FROM
tblOrder AS O
INNER JOIN tblParcelAssigned AS PA ON O.OrderID = PA.OrderID
WHERE
CreatedWhen IS NULL OR ScannedWhen IS NULL
GROUP BY
O.OrderId
SELECT
O.OrderId,
COUNT(*) AS TotalProblemPackages,
SUM(CASE WHEN CreatedWhen IS NOT NULL AND ScannedWhen IS NULL THEN 1 ELSE 0 END) AS NotScannedOut,
SUM(CASE WHEN CreatedWhen IS NULL AND ScannedWhen IS NOT NULL THEN 1 ELSE 0 END) AS NotScannedIn
FROM
tblOrder AS O
INNER JOIN tblParcelAssigned AS PA ON O.OrderID = PA.OrderID
GROUP BY
O.OrderId
HAVING
SUM(CASE WHEN CreatedWhen IS NOT NULL AND ScannedWhen IS NULL THEN 1 ELSE 0 END) > 0
OR
SUM(CASE WHEN CreatedWhen IS NULL AND ScannedWhen IS NOT NULL THEN 1 ELSE 0 END) > 0

The relationship is one or more parcels for each order. The relatonship is something like this: Order ->(Many)Parcels -> ParcelsAssigned (Many). I skipped the Parcels table because the "meat" is in ParcelsAssigned, and both Parcels and ParcelsAssigned can be linked to the Order table.

Sometimes people do not scan packages in but they do scan them out. Sometimes they scan them in but do not scan them out. I am thinking that the second query should work well, but will be experimenting with variations of all three. I have a long afternoon ahead of me, but this has been an awesome learning experience. Thanks again to everyone here.

I've been asked to add a column which looks for all delivered orders without scanned signatures. The conditions are slightly different from the original ones. The existance of a signature is in a table attached to my orders.

I have the query pulling up the expected records using a list of conditions using OR, but I'm wondering if this is the most efficient way to do it as there seems to be a lot of repetion. Others have recommended using a a UNION SELECT instead. Which is better?

ALSO, I'm wondering if I can use the SUM(Case part of the SELECTed columns to display a count of delivered orders without scanned signatures. What is the syntax used to do this? I've experimented with CASE WHEN EXISTS (SELECT followed by the conditions but haven't had any luck.

This is the functioning code so far. The third CASE statement only counts some occurrances since it is not selecting values based on the OR conditions at the end of the query.

SELECT O.OrderId, O.CustID, O.OriginName, OS.POD, OD.DriverId, COUNT(*) AS TotalProblemPackages, SUM(CASE WHEN pa.CreatedWhen IS NOT NULL AND pa.ScannedWhen IS NULL and pa.type = 3 THEN 1 ELSE 0 END) AS NotScannedIn, SUM(CASE WHEN pa.CreatedWhen IS NULL AND pa.ScannedWhen IS NOT NULL and pa.type = 2 THEN 1 ELSE 0 END) AS NotScannedOut, SUM(CASE WHEN pa.CreatedWhen IS not NULL AND pa.ScannedWhen IS not NULL and pa.type = 3 THEN 1 ELSE 0 END) AS MissingSignature