CODEWITHSUNDEEP
sqlsql-server-2012
Geto
Geto

Reputation: 63

Calculations and Joins with Multiple Tables

I have a Point of Sale system that I'm trying to write a SQL query in a way that I can see check totals by tenders to reconcile with cash flows. Need to export the data to Excel so I can do final calculations. Unfortunately the schema is not mine and can't change it. One problem I ran into is that there are cases where one check has multiple transactions involving various tenders, therefore I need to do some operations to get the correct totals.

The schema and sample data is here: http://www.sqlfiddle.com/#!18/d28656

The Excel table will look like this:

CHECKID TAX PAYMENT TENDER AMOUNT
1 0.35 5.35 CASH 5.35
2 1.40 21.40 VISA 10.70
2 MC 10.70

Ultimately, the last Excel total row (which I can write) should look like this:

Unique Checks = 2
Total Tax = 1.75
Total Payments = 26.75
Total CASH = 5.35
Total VISA = 10.70
Total MC   = 10.70

Upvotes: 0

Views: 73

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

This answers the original version of the question (which refers to one table).

If you are content with all the values in a single row, just use aggregation:

select count(distinct checkid),
       sum(tax),
       sum(payments),
       sum(case when tender = 'CASH' then amount else 0 end) as cash,
       sum(case when tender = 'VISA' then amount else 0 end) as visa,
       sum(case when tender = 'mc' then amount else 0 end) as mc
from t;

Upvotes: 1

Related Questions