Grouping total sales by payment type

Question

Post reply

Grouping total sales by payment type

Stephen Francis

SSC Enthusiast

Points: 175
More actions
October 14, 2006 at 12:56 am

#117209

Hi All
I have a sales table with columns..
mydate,      InvoiceNo,   Cash, Cheque,   CC,    Account, Total, Gst etc
1/10/2006    1                 20     0          20       0           40      4
2/10/2006    2                 0       30        0         0           30      3
How can I group totals by payment type over a particular mydate range?
I want to graph total cash, total cheque, Total CC sales etc over a selectable date range
Regards
Steve

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 1

Should the totals be by day for the range or are you just looking for a grand total for the range?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Stephen Francis SSC Enthusiast Points: 175 More actions · Answer 2

Hi jeff

Just a grand total for the range

Regards

Steve

Sergiy SSC Guru Points: 110208 More actions · Answer 3

So, what's a problem?

SELECT SUM(Cash) + SUM(Cheque) + SUM(CC) + SUM(Account) + SUM(Total) + SUM(Gst) + etc

WHERE mydate >= @StartDate and mydate < @EndDate+1

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 4

Stephen,

If you want separate totals for each payment type, then this would do...

SELECT SUM(Cash) AS TotalCash,

SUM(Cheque) AS TotalCheque,

SUM(CC) AS TotalCC,

SUM(Account) AS TotalAccount

SUM(Total) AS GrandTotal

SUM(Gst) AS TotalGST,

Etc,

WHERE MyDate >= @StartDate

AND MyDate < @EndDate+1

Do note that in both examples above, we're assuming that @StartDate and @EndDate are datetime variables that hold a date with no time (actually, a date with a time of midnight) or you may get unexpected results.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Stephen Francis SSC Enthusiast Points: 175 More actions · Answer 5

Hi Jeff / Sergie

Thanks for the replies

I should have been more specific with my problem

The above examples work fine but only return the data as 1 record

To graph the payment types I need the data in multi row form e.g

Payment Type Total

Cash $500

Cheque $100

CC $1000

Then the PAyment type becomes the graphs x axis (Category) and the Total becomes the Y axis (Values)

I have a solution working by pulling each total up 1 at a time and saving to a VB.net datatable

It is not very elegant however

Regards

Steve

Sergiy SSC Guru Points: 110208 More actions · Answer 6

1. Your table design is wrong.

It must be:

InvoiceID int,

PaymentDate datetime,

PaymentTypeID smallint,

PaymentAmount money

2. Does not matter. Fix No.1.

_____________
Code for TallyGenerator

hallruss Valued Member Points: 55 More actions · Answer 7

Sergiy is correct about the poor design but :

SELECT 'Cash' AS PaymentType, SUM(Cash) AS [Total] FROM Ledger WHERE mydate BETWEEN...

UNION ALL

SELECT 'Cheque', SUM(Cheque) FROM Ledger WHERE mydate BETWEEN...

UNION ALL

SELECT 'CC', SUM(CC) FROM Ledger WHERE mydate BETWEEN...

UNION ALL

SELECT 'Account', SUM(Account) FROM Ledger WHERE mydate BETWEEN...

UNION ALL

SELECT 'GST', SUM(GST) FROM Ledger WHERE mydate BETWEEN...

Stephen Francis SSC Enthusiast Points: 175 More actions · Answer 8

Hi all

Thanks for the replies, most appreciated that you take the time

I don't think the table design is wrong

InvoiceNo int,

PaymentDate datetime,

PaymentAmount money

Cash Money

CC Money

Cheque Money etc

As payments for a transaction can be multiple types I have to break it down to component payments as well as the total

Please let me know if I am wrong as I can't see how to cover the situation of multiple payment types otherwise

Regards

Steve

Rolando V. Encarnacion Newbie Points: 3 More actions · Answer 9

Nice trick Russell.

I was almost done with my solution to a similar problem until i saw your post. Your query is simple, easy to maintain, and it does the job.

Sergiy SSC Guru Points: 110208 More actions · Answer 10

Did you ever pay for something by cash and by credit card and by check?

Can you describe the circumstances?

It's very interesting to hear about such experience.

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110208 More actions · Answer 11

Yes, it does the job.

But only if your database does not exceed several thousands of records.

As soon as it will become more than Excel spreadsheet you'll face performance issues.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 12

No. Serqiy is correct... the table design is wrong... if you take multiple payments for a given invoice, you should have multiple rows.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 13

Looks like you are trying to normalize and denormalized report data and sum it up.

Steven, Is this how the table is designed. what do u do if u have another payment method?

It looks like it been build for a specific reporting purpose.

Stephen Francis SSC Enthusiast Points: 175 More actions · Answer 14

Hi All

Thanks for the many comments

I have a purpose built POS application, the mentioned payment types are fixed

Admittedly the multiple payment type option is probably excessive, but I have encountered many times somebody paying by cash on hand and the balance by CC or Eftpos

My table setup is currently a Sales table with the date payment types and total. I also have a salesdetails table which holds the sale items details and cost. They are linked by the 'invoicenumber' field

Are you saying I should have a 3rd table to store the payment types for an invoice instead of fields in the sales table?

Regards

Steve