Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Query Syntax

Posted on 2016-08-04
12
Medium Priority
?
124 Views
Last Modified: 2016-08-07
Hello Experts,

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]

Open in new window



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
0
Comment
Question by:triphen
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
12 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 41741905
Didn't see anything wrong. Can you post your query result and expected output?
For average, you can use AVG function.
0
 
LVL 26

Expert Comment

by:wilcoxon
ID: 41742566
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
0
 

Author Comment

by:triphen
ID: 41742952
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 columnspunchpayroll tablePOSHeader.PNG
Storeinfo.PNG
Current-Results.PNG
Labor-Removed---Correct.PNG
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 26

Expert Comment

by:wilcoxon
ID: 41743030
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.
0
 

Author Comment

by:triphen
ID: 41743293
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?

"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
0
 

Author Comment

by:triphen
ID: 41743294
It wont let me attach: "An error occurred with the server. Please try again later."
0
 

Author Comment

by:triphen
ID: 41743341
Working now, attached :)
posheader.csv
punchpayroll.csv
storeinfo.csv
0
 
LVL 26

Expert Comment

by:wilcoxon
ID: 41743584
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).
0
 

Author Comment

by:triphen
ID: 41743634
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!
0
 

Author Comment

by:triphen
ID: 41745052
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.
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 41745474
Try aggregating the tables independently so that the numbers are calculated before rows get multiplied by joining the tables.
SELECT
      storeinfo.description   AS [Store Name]
    , ph.nettotal             AS [Net Total]
    , ph.tcount               AS [Transaction Count]
    , ph.nettotal / ph.tcount AS [Transaction Average]
    , pl.[Labor]
FROM storeinfo
      INNER JOIN (
            SELECT
                  posheader
                , SUM(nettotal)   AS nettotal
                , COUNT(transact) AS tcount
            FROM posheader
            WHERE posheader.status = 3
                  AND posheader.opendate = 20160802
            GROUP BY
                  snum
      ) ph ON storeinfo.storenum = ph.snum
      INNER JOIN (
            SELECT
                  snum
                , SUM(totalwage) AS Labor
            FROM punchpayroll
            WHERE opendate = 20160802
            GROUP BY
                  snum
      ) pl ON storeinfo.storenum = pl.snum
GROUP BY
      storeinfo.description
ORDER BY
      storeinfo.description

Open in new window

0
 

Author Closing Comment

by:triphen
ID: 41746385
Thanks!
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question