PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
How to use SUM() and Group By?-VBForums

# Thread: How to use SUM() and Group By?

1. ## How to use SUM() and Group By?

Hello experts.
Because my english is poor, I help my self with the picture.

I need to calculate the unpaid sum.
The unpaid or sum(reste) is 10 + 25.

What I did is:
Code:
```Select Sum(Reste) As TotalSum from Rec_tbl group BY Cs_ID
Debug.Print TotalSum```
The output is: 70 instead of 35
Thank you

2. ## Re: How to use SUM() and Group By?

Why would you expect 35?

The first ID totals 70 40+20+10 and the second totals 65

3. ## Re: How to use SUM() and Group By?

Originally Posted by DataMiser
Why would you expect 35?

The first ID totals 70 40+20+10 and the second totals 65
Thanks sir
yes but without duplication I expect 10 + 25
That's what I expect from Group By

4. ## Re: How to use SUM() and Group By?

Originally Posted by DataMiser
Why would you expect 35?

The first ID totals 70 40+20+10 and the second totals 65
DM, yeah i fell for that one too until figured it out
Row
1 --> Pay=60 --> Total Pay 60 --> Rest 40 (Tarif 100 - Total Pay 60)
2 --> Pay=20 --> Total Pay this 20 + the 60 from Total Pay in Row 1 = 80 --> Rest 20 (Tarif 100 - Total Pay 80)
3 --> Pay=10 --> Total Pay this 10 + the 80 from Total Pay in Row 2 = 90 --> Rest 10 (Traif 100 - Total Pay 90)

As far as i understood: He needs the Sum-Total of Rest, which has been not paid for the Tarif/ID

5. ## Re: How to use SUM() and Group By?

Originally Posted by Mustaphi
Hello experts.
Because my english is poor, I help my self with the picture.

I need to calculate the unpaid sum.
The unpaid or sum(reste) is 10 + 25.

What I did is:
Code:
```Select Sum(Reste) As TotalSum from Rec_tbl group BY Cs_ID
Debug.Print TotalSum```
The output is: 70 instead of 35
Thank you
I'm going to take a stab at it (Not tested)

SQL Code:
`SELECT SUM(Reste) AS SumReste FROM (SELECT Cs_ID, Tarif, SUM(Pay) AS SumPay, (Tarif-SumPay) AS Reste FROM MyTable GROUP BY Cs_ID, Tarif)`

6. ## Re: How to use SUM() and Group By?

Your query may work (except, of course, 'MyTable' is "Rec_tbl'), but that surely is a convoluted way to keep accounting records. The CS_Id is confusing as well---it is '1' for two different Prod_Id's.

My suggestion would be to redo the table so that when transactions are done, no 'calculation' in the table is accomplished...do all that with code. There are just too many confusing columns in that table.

7. ## Re: How to use SUM() and Group By?

Originally Posted by SamOscarBrown
*snipp* The CS_Id is confusing as well---it is '1' for two different Prod_Id's.

*snipp*
probably because Cs_ID and Prod_ID are Foreign-Keys to a Customer- and a Product-Table.
It's the most simple way for a m:n-Relationship.
I think he's trying to find out, what the "Balance" is for each Customer

8. ## Re: How to use SUM() and Group By?

It would seem to make a lot more sense if there were a header and detail table for orders. Makes no sense to have totals on line items.

Header would contain customer id, order number, total of all line items, total payments, and while not required could also contain the amount due though that could be calculated at run time and not use the space in the db.

Detail table would contain order number and line item info nothing payment related.

You would then run your query on the header and easily get the results you seek.

9. ## Re: How to use SUM() and Group By?

Originally Posted by Mustaphi
Thanks sir
yes but without duplication I expect 10 + 25
That's what I expect from Group By
There are not duplication, all rows are different.
I agree 100% with DataMiser.

Mustaphi, your query is wrong.

10. ## Re: How to use SUM() and Group By?

Originally Posted by Zvoni
DM, yeah i fell for that one too until figured it out
Row
1 --> Pay=60 --> Total Pay 60 --> Rest 40 (Tarif 100 - Total Pay 60)
2 --> Pay=20 --> Total Pay this 20 + the 60 from Total Pay in Row 1 = 80 --> Rest 20 (Tarif 100 - Total Pay 80)
3 --> Pay=10 --> Total Pay this 10 + the 80 from Total Pay in Row 2 = 90 --> Rest 10 (Traif 100 - Total Pay 90)

As far as i understood: He needs the Sum-Total of Rest, which has been not paid for the Tarif/ID
Thank you sir for understanding my needs.

11. ## Re: How to use SUM() and Group By?

Originally Posted by Zvoni
I'm going to take a stab at it (Not tested)

SQL Code:
`SELECT SUM(Reste) AS SumReste FROM (SELECT Cs_ID, Tarif, SUM(Pay) AS SumPay, (Tarif-SumPay) AS Reste FROM MyTable GROUP BY Cs_ID, Tarif)`
Thanks a lot for the code.

Though the query looks complicated but you got it.
Your code is working as expected.
Thanks again

12. ## Re: How to use SUM() and Group By?

DataMiser
You suggestion is surely bright but It seems complicated for me.
I'm satisfied with what Zvoni has suggested.

13. ## Re: How to use SUM() and Group By?

Now suppose I want to calculate the unpaid sum for each record.

For example Record 1 : the unpaid sum is 10
Record 2 : the unpaid sum is 25
How would be the query please?

14. ## Re: How to use SUM() and Group By?

Just Line2 of my SQL-Query

15. ## Re: How to use SUM() and Group By?

Originally Posted by DataMiser
It would seem to make a lot more sense if there were a header and detail table for orders. Makes no sense to have totals on line items.

Header would contain customer id, order number, total of all line items, total payments, and while not required could also contain the amount due though that could be calculated at run time and not use the space in the db.

Detail table would contain order number and line item info nothing payment related.

You would then run your query on the header and easily get the results you seek.
DM, agree with you except one thing: I'd consider a Field in the customer-table called "Balance" (well, it's at least the first idea. If it would be the customer-table or a separate Detail-Table with a 1:1-relation is up for discussion).

Why? Right now i have to help out the management of our skydiving-club.
We're using a Software (bought software, not written by me) which keeps track of Lifts, Gear, Ratings, amount of Jumps per Skydiver and Price/charge per Jump.
That software calculates the balance during runtime (as you suggested), and i'm going spare writing the SQL's in MySQL-Workbench to find out if there are members which are in the "red zone" moneywise.
And all because that software cannot produce a report across all club-members. Yes per single member we can generate a report and what not, but not a list (and then Export to Excel).
Drives me barmy to figure out the way the software calculates everything by just looking at the raw data

16. ## Re: How to use SUM() and Group By?

Originally Posted by Zvoni
Just Line2 of my SQL-Query
Thank you very much
it worked

17. ## Re: How to use SUM() and Group By?

Originally Posted by SamOscarBrown
Your query may work (except, of course, 'MyTable' is "Rec_tbl'), but that surely is a convoluted way to keep accounting records. The CS_Id is confusing as well---it is '1' for two different Prod_Id's.

My suggestion would be to redo the table so that when transactions are done, no 'calculation' in the table is accomplished...do all that with code. There are just too many confusing columns in that table.
Thank you for your interesr sir
My table Rec_tbl is a child table it is linked with a parent table through a FK
Field 1 represents the record's ID.
Field 2 represents the Paiement ID (autoincrement) each time there a new payment
(The picture shows a mistake because the second field is autoincrement)
Fiels 3 represents the Price
Field 4 = the payment by portion
field 4 = sum of payment
field 5 = the rest

I can't figure out another way to rebuild the table.
Please if you can help me with a simple way to rebuild my table, I would be grateful to you.
Thank you

18. ## Re: How to use SUM() and Group By?

Originally Posted by Mustaphi
Thank you for your interesr sir
My table Rec_tbl is a child table it is linked with a parent table through a FK
Field 1 represents the record's ID.
Field 2 represents the Paiement ID (autoincrement) each time there a new payment
(The picture shows a mistake because the second field is autoincrement)
Fiels 3 represents the Price
Field 4 = the payment by portion
field 4 = sum of payment
field 5 = the rest

I can't figure out another way to rebuild the table.
Please if you can help me with a simple way to rebuild my table, I would be grateful to you.
Your fields 4 and 5 (Total_Pay and Reste) should not exist as Fields in a DB-Table (if at all, then in a View-Def).

IMO you should split this up into two tables.

One which describes "who is owing something, and what the amount is" (e.g. name it Tarifs or "Debts"):
Table Tarifs:
- ID AutoIncrement
- Cust_ID
- Amount

...in the context of your example, this table would only contain two records:
ID, Cust_ID, Amount
1, 1, 100
2, 2, 50

In the second table, you only gather the partial payments (e.g. name it Tarifs_Payments):
Table Tarifs_Payments:
- ID AutoIncrement
- Tarifs_ID
- Payment

...in the context of your example, this table would only contain 5 records in 3 Cols only:
ID, Tarifs_ID, Payment
1, 1, 60
2, 1, 20
3, 1, 10
4, 2, 10
5, 2, 15

With only that information in the two tables, you can easily construct Views, which show you
columns for the "development of partial payments and the rest" on any given Tarifs_ID.

Olaf

19. ## Re: How to use SUM() and Group By?

I see Olaf did what you asked me (also by PM)...he is a much, much better programmer than I would ever become...and just perusing his response, I'd say he thought it out nicely and gave you THE answer (I might have goofed around some before coming up with something similar).

20. ## Re: How to use SUM() and Group By?

Olaf appears to have forgotten about the ProdID which suggests to me that you need something akin to an Orders table somewhere in your database design, as well as a products table which holds prices for those products. Then, the original sum owing would be the total value of the products ordered.

21. ## Re: How to use SUM() and Group By?

Originally Posted by ColinE66
Olaf appears to have forgotten about the ProdID which suggests to me that you need something akin to an Orders table somewhere in your database design, as well as a products table which holds prices for those products. Then, the original sum owing would be the total value of the products ordered.
Right, all this can be handled separately in additional tables, without blowing the Tarifs-Table up by much.

E.g. let's assume, some Customer buys a car (which is listed with ID=12 and a "recommended price" in a Products-Table for - say - 10000).

Some Customers will perhaps pay that recommended price in full immediately, so that the Orders-Table might contain:
ID, Prod_ID, Cust_ID, Negotiated_Price, Payed_Immediately, Tarifs_ID
1, 12, 1, 10000, 10000, <null>

The next customer negotiates hard (from 10000 down to 9000), and only wants to pay an advance of 50%, the rest in rates.
ID, Prod_ID, Cust_ID, Negotiated_Price, Payed_Immediately, Tarifs_ID
2, 12, 2, 9000, 4500, 3

Now, the above record contains also a Tarifs_ID with a value <> null (since the rest of the negotiated price is being paid later, and thus the tarifs-table got a new entry).

So, the coupling of a "Debts-Table" (or Tarifs_Table) to a certain Orders-Table can be achieved in different ways
(in the example above I've put the reference-ID into the Orders-Table itself - but a separated Table like "Orders_Debts" (Orders_ID, Tarifs_ID)
would also be possible of course.

What a proper "Debts_Table" might need in addition, is perhaps information about other details (like interest-rates, payment-intervals or other annuity-parameters).

Olaf

22. ## Re: How to use SUM() and Group By?

Thank you all of you for your precious ideas.
I was surprised to see such big number of experts who are interested in my worry and want to help me.
I'm so grateful to you.
I think what Mr. Schmidt is very interesting.
I will take a pen and paper and try to figure out how to put all this into practice.

However, before I start with that, I need some clarification concerning the calculation of the rest. (the unpaid portions)

The next customer negotiates hard (from 10000 down to 9000), and only wants to pay an advance of 50%, the rest in rates.
 ID Prod_ID Cust_ID Negotiated_Price Payed_Immediately Tarifs_ID 2 12 2 9000 4500 3
So it is clear that this record has paid 50% ant still 50% unpaid.
My worry is where am I going to store this unpaid sum?
Because later I need to calculate all the debts of each record and the overall debts of all records.
thanks

23. ## Re: How to use SUM() and Group By?

You do not need to store it, you can calculate it within the query
UnpaidAmount=(Negotiated_Price - Payed_Immediately)

24. ## Re: How to use SUM() and Group By?

The next customer negotiates hard (from 10000 down to 9000), and only wants to pay an advance of 50%, the rest in rates.
 ID Prod_ID Cust_ID Negotiated_Price Payed_Immediately Tarifs_ID 2 12 2 9000 4500 3

Originally Posted by Mustaphi
So it is clear that this record has paid 50% ant still 50% unpaid.
My worry is where am I going to store this unpaid sum?
The principal table-layout for such a "Debts" or "Loans" or "Credits" or "Tarifs"-table was already shown (in #18):
One which describes "who is owing something, and what the amount is" (e.g. name it Tarifs or "Debts"):
Table Tarifs:
- ID AutoIncrement
- Cust_ID
- Amount
If you don't care about "interest-rates" (if you hand-out only 0%-loans) you could leave that table-layout as it is.
Oh - and the "extra Cust_ID" Field in the above table becomes explainable, when you consider that perhaps
"ones grandad" was the customer, who placed the order - and paid the advance generously, ... but then leaves his grandson,
to pay the rest in rates for the still owed sum... (so, the "loan-contract" is really between the grandson and the car-shop, using a different Cust_ID).

So, (to answer your question) one places the "rest-sum" (in this case 4500) in the Amount-Field of the above table...
(together with the ID of the Customer, who will be responsible for the future payments).

Well, to do that table-layout "right", one might want to add some extra-fields to it (in case you don't want to hand out 0%-loans generally).

For the 0%-case, you could calculate a monthly rate (let's say for two years = 24months) simply like:
? 4500 / 24 = 187.50

Which is BTW the same value, you'll get (in VBs immediate-window), when you use VBs PMT-function with a 0.00 percent-value as shown below.
?-PMT(0.00 / 12, 24, 4500)
187.5

Though in case you want to hand out these loans with an interest-rate <> 0 (e.g. 7% per year),
you could use the PMT-function to calculate the correct "monthly rate" also for that case:
?-PMT(0.07 / 12, 24, 4500)
201.476...

It'd be up to you, to store these "finance-math" arguments in your loan-table in addition...

Olaf

25. ## Re: How to use SUM() and Group By?

So grateful to you Mr. Schmidt
Thank yo for your time and help
I'll keep the thread open so that in case I face problems, I'll be back
thank you again Mr. Schmidt

26. ## Re: How to use SUM() and Group By?

Hello Mr. Schmidt
I have been trying for four days to implement your plan but I got confused.
Please if possible you could summarize that in a simple English.
I’m going to summarize my needs.
Let’s say Customer XXX: buys two articles: A mobile and a laptop

1) Mobile

2- Laptop

I need to know how many tables do I need?
Keeping in mind that I need later to track each customer’s articles, payments ant rests.
and display them like that :

Als0 I need to sum the "Rests" for each customer and the total rests for all customers.

So far I have one table for Customers and one for Articles and one for orders.
But I got confused with the last table.
Thank you sir

27. ## Re: How to use SUM() and Group By?

You should have a payments table that relates to Orders. Do you have that?

28. ## Re: How to use SUM() and Group By?

Originally Posted by ColinE66
You should have a payments table that relates to Orders. Do you have that?
So you think I should add a fourth table?

29. ## Re: How to use SUM() and Group By?

Yes! Your database design should employ a strategy where one row represents one real-life 'thing' or concept.

So, a Customers table where each row represents a single customer, a Products table where each row represents a single Product, an Orders table where each row represents a single Order for a single Product, placed by a single Customer on a single Date & Time. Finally a Payments table where each row represents a single payment against an Order.

You can deviate from that a little bit depending on how the business is run, but that is the general idea.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•

Featured

Click Here to Expand Forum to Full Width