triphen
asked on
SQL Query Syntax
Hello Experts,
Running the SQL query below is inflating numbers for net total, transaction count, and labor. Not sure why...
Ideas?
I believe it may have to do with the "transact" column but it doesn't exist in storeinfo or punchpayroll tables.
SQL Anywhere v10
Running the SQL query below is inflating numbers for net total, transaction count, and labor. Not sure why...
SELECT
storeinfo.description AS [Store Name],
SUM(posheader.nettotal) AS [Net Total],
COUNT(transact) AS [Transaction Count],
[Net Total] / [Transaction Count] AS [Transaction Average],
SUM(totalwage) AS [Labor]
FROM
posheader
INNER JOIN storeinfo on posheader.snum = storeinfo.storenum
INNER JOIN punchpayroll on posheader.snum = punchpayroll.snum
WHERE
posheader.status = 3 AND
posheader.opendate = 20160802 AND
punchpayroll.opendate = 20160802
GROUP BY
[Store Name]
ORDER BY
[Store Name]
Ideas?
I believe it may have to do with the "transact" column but it doesn't exist in storeinfo or punchpayroll tables.
SQL Anywhere v10
I'd also suggest using AVG. I also don't see anything wrong. We may need the following in order to help you:
- The query result and expected output for at least 1 row
- The underlying data from the 3 tables that contribute to that row
ASKER
See attached for the 3 tables.
See attached for the current results.
See attached for the correct results, it happens when I remove the Labor join and its columnsPOSHeader.PNG
Storeinfo.PNG
Current-Results.PNG
Labor-Removed---Correct.PNG
See attached for the current results.
See attached for the correct results, it happens when I remove the Labor join and its columnsPOSHeader.PNG
Storeinfo.PNG
Current-Results.PNG
Labor-Removed---Correct.PNG
It looks to me like either punchpayroll contains data you're not expecting or else (more likely) you're missing a second join condition for punchpayroll (when you include punchpayroll, the counts and sums go up much higher than you expect).
Is it possible to provide the data from all 3 tables for storenum/snum = 3 (or a different one if there is one with even less rows of data)? The schemas don't help that much.
Does it make any difference if you change the Transaction Average calculation to use AVG? It shouldn't but it's something to try.
Is it possible to provide the data from all 3 tables for storenum/snum = 3 (or a different one if there is one with even less rows of data)? The schemas don't help that much.
Does it make any difference if you change the Transaction Average calculation to use AVG? It shouldn't but it's something to try.
ASKER
I don't think AVG is the issue but not sure how to even syntax that from [Net Total] / [Transaction Count] AS [Transaction Average]
anyway...
I am sure it has to do with missing a second join, the question is what?
Attached
anyway...
I am sure it has to do with missing a second join, the question is what?
"Is it possible to provide the data from all 3 tables for storenum/snum = 3 (or a different one if there is one with even less rows of data)? "
Attached
ASKER
It wont let me attach: "An error occurred with the server. Please try again later."
ASKER
Can you also provide what the primary key is for each table? It might also be helpful to have any other indexes. Unfortunately, I have a really busy weekend so I may not be able to get to this until next week (but I'll try).
ASKER
Primary Key for POSHeader = transact, snum
Primary Key for StoreInfo = storenum, snum
PunchPayroll = this is actually a view and not a table. Maybe this is problem? The table it references is called punchclock and that has a primary key of uniqueid and snum
Thanks for your time!
Primary Key for StoreInfo = storenum, snum
PunchPayroll = this is actually a view and not a table. Maybe this is problem? The table it references is called punchclock and that has a primary key of uniqueid and snum
Thanks for your time!
ASKER
It looks like the transact column is what is repeated over and over when I remove the sum and count aggregations. This must be a clue as to what's going on.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks!
For average, you can use AVG function.