How to properly Count and Group a calculation in SQL

[niceOutside]

Libre: 7.1.8.1
Windows 10
HSQLDB Embedded

Hello, I’m new to Libre Base and SQL. So I apologize if this has already been covered. I am having an issue with a calculation in SQL. I want to Counting Distinct Transactions then divide Hours Worked.

“Transactions Per Hour” should only count: Distinct “tbl_TRX”.“TRX #”

This SQL does not work.
COUNT( DISTINCT “tbl_TRX”.“TRX #” ) / ( “tbl_EMP”.“Hours” ) AS “Transactions Per Hour”
Error: Not in aggregate function or group by clause: org.hsqldb.Expression

This SQL works fine when by itself.
COUNT( DISTINCT “tbl_TRX”.“TRX #” ) AS “TRX Count”

SQL Below: Error: Not in aggregate function or group by clause: org.hsqldb.Expression

SELECT DISTINCT "tbl_EMP"."Date", "tbl_EMP"."Personnel"
  , COUNT( "tbl_TRX"."TRX #" ) AS "Items Sold"
  , COUNT( "tbl_TRX"."TRX #" ) AS "TRX Count"
  , "tbl_EMP"."Hours"
  , COUNT( "tbl_TRX".* ) / ( "tbl_EMP"."Hours" ) AS "Items Per Hour"
  , COUNT( DISTINCT "tbl_TRX"."TRX #" ) / ( "tbl_EMP"."Hours" ) AS "Transactions Per Hour"
  , SUM( "tbl_TRX"."Sold Price" ) AS "Total Sold" 

FROM { oj "tbl_EMP" LEFT OUTER JOIN "tbl_TRX" ON "tbl_EMP"."Personnel" = "tbl_TRX"."Cashier" 
  AND "tbl_EMP"."Date" = "tbl_TRX"."Date" } 
  WHERE ( "tbl_EMP"."Position" = 'Cashier' 
  AND ( "tbl_EMP"."Date" = :Var_StDtMx OR :Var_StDtMx IS NULL ) ) 

GROUP BY "tbl_EMP"."Personnel", "tbl_EMP"."Hours", "tbl_EMP"."Date" 

ORDER BY "Items Per Hour" DESC

• Side Note
  Although the event of an Empolyee having zero transactions is unlikely, their Transactions Per Hour should read zero. Hopefully counting Distinct TRX #'s will solve this problem.

Attached is the Base file in question. I’m using a macro written by Ratslinger to refresh all forms, and set a default date.

TotalRecall.odb (32.7 KB)

[Ratslinger]

Hello,
It appears there are, just on the surface, a number of questionable items.

COUNT( "tbl_TRX"."TRX #" ) AS "Items Sold"
COUNT( "tbl_TRX"."TRX #" ) AS "TRX Count"

are the same thing with different alias names.
.
And this is questionable:

COUNT( DISTINCT "tbl_TRX"."TRX #" ) / ( "tbl_EMP"."Hours" ) AS "Transactions Per Hour"

So you are saying here that a TRX# with 14 items has the same weight as one with one item?
.
Here:

SUM( "tbl_TRX"."Sold Price" ) AS "Total Sold"

there is nothing about Sales vs Returns. You’re just adding all together.
.
This is a first look and possibly I have overlooked something in what you have presented
here.

[niceOutside]

And this is questionable:

COUNT( DISTINCT "tbl_TRX"."TRX #" ) / ( "tbl_EMP"."Hours" ) AS "Transactions Per Hour"

So you are saying here that a TRX# with 14 items has the same weight as one with one item?

Yes, I should have explained that better.

The Point of Sale program gives us a report with every item sold assigned a “TRX #”, if two or more items have the same “TRX #” then that is one transactions.

Thanks for pointing out the fact that I’m adding up the Returns as well. Will probably add a column for Total Returns.

[Ratslinger]

Hello,
Without the questions in the comment answered (likely you can adjust) here is what I arrived at:

.
Here is the SQL:

SELECT "Date",
       "Personnel",
       "Position",
       "Hours",
       COALESCE("Distinct" / "Hours", 0) AS "Transactions Per Hour",
       COALESCE("Items",0) "Items Sold",
       COALESCE(("Items" / "Hours"),0) "Items Per Hour",
       COALESCE("Total Sold", 0) "Total Sold"
FROM "tbl_EMP"
  LEFT JOIN (SELECT "Cashier",
                    "Date" "TRN_Date",
                    COUNT("tbl_TRX"."TRX #") AS "Items",
                    COUNT(DISTINCT "tbl_TRX"."TRX #")"Distinct",
                    SUM("Sold Price") AS "Total Sold"
             FROM "tbl_TRX"
             GROUP BY "Cashier",
                      "Date") "a"
         ON "tbl_EMP"."Personnel" = "a"."Cashier"
        AND "tbl_EMP"."Date" = "a"."TRN_Date"
WHERE ("tbl_EMP"."Position" = 'Cashier' AND ("tbl_EMP"."Date" = :Var_StDtMx OR:Var_StDtMx IS NULL))

[niceOutside]

Thanks, I’ll try to implement this!

[niceOutside]

This works great as expected! Thanks for taking the time solve this for me. I have learned just about everything I know about Base and SQL from you and TheFrugalComputerGuy on YouTube.

Is there a SUMIFS function for Base like in Calc? Or can I put a WHERE or IF in the SUM function?

I’m trying to add a SUM column for “Total Returns” that only SUMs when “tbl_TRX”.“TRX Type” = ‘Return’.

I’m thinking of something like this… but Syntax is clearly wrong.
SUM (“Sold Price”) WHERE “tbl_TRX”.“TRX Type” = ‘Sale’) AS “Total Sold”
SUM (“Sold Price”) WHERE “tbl_TRX”.“TRX Type” = ‘Return’) AS “Total Returns”

Thanks again for your help!

[Ratslinger]

OK this, using Case statement works from the test I have done:

SELECT "Date",
       "Personnel",
       "Position",
       "Hours",
       COALESCE("Distinct" / "Hours", 0) AS "Transactions Per Hour",
       COALESCE("Items",0) "Items Sold",
       COALESCE(("Items" / "Hours"),0) "Items Per Hour",
       COALESCE("Sold", 0)"Sold",
       COALESCE("Returned",0)"Returned"
FROM "tbl_EMP"
  LEFT JOIN (SELECT "Cashier",
                    "Date" "TRN_Date",
                    COUNT("tbl_TRX"."TRX #") AS "Items",
                    COUNT(DISTINCT "tbl_TRX"."TRX #")"Distinct",
                    SUM(CASE WHEN "TRX Type" = 'Sale' THEN "Sold Price"ELSE 0 END) AS "Sold",
                    SUM(CASE WHEN "TRX Type" = 'Return' THEN "Sold Price"ELSE 0 END) AS "Returned"
             FROM "tbl_TRX"
             GROUP BY "Cashier",
                      "Date") "a"
         ON "tbl_EMP"."Personnel" = "a"."Cashier"
        AND "tbl_EMP"."Date" = "a"."TRN_Date"
WHERE ("tbl_EMP"."Position" = 'Cashier' AND ("tbl_EMP"."Date" = :Var_StDtMx OR:Var_StDtMx IS NULL))

[niceOutside]

Thank You! This works great!