-
Jun 8th, 2018, 06:18 PM
#1
Thread Starter
Fanatic Member
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
-
Jun 8th, 2018, 08:58 PM
#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
Last edited by DataMiser; Jun 8th, 2018 at 09:02 PM.
-
Jun 8th, 2018, 09:08 PM
#3
Thread Starter
Fanatic Member
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
-
Jun 9th, 2018, 06:46 AM
#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
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Jun 9th, 2018, 06:53 AM
#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)
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Jun 9th, 2018, 07:08 AM
#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.
-
Jun 9th, 2018, 07:52 AM
#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
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Jun 9th, 2018, 09:54 AM
#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.
-
Jun 9th, 2018, 10:01 AM
#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.
-
Jun 9th, 2018, 12:22 PM
#10
Thread Starter
Fanatic Member
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.
-
Jun 9th, 2018, 12:29 PM
#11
Thread Starter
Fanatic Member
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
-
Jun 9th, 2018, 12:33 PM
#12
Thread Starter
Fanatic Member
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.
-
Jun 9th, 2018, 12:41 PM
#13
Thread Starter
Fanatic Member
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?
-
Jun 9th, 2018, 02:21 PM
#14
Re: How to use SUM() and Group By?
Just Line2 of my SQL-Query
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Jun 9th, 2018, 02:39 PM
#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
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Jun 9th, 2018, 03:05 PM
#16
Thread Starter
Fanatic Member
Re: How to use SUM() and Group By?
Originally Posted by Zvoni
Just Line2 of my SQL-Query
Thank you very much
it worked
-
Jun 9th, 2018, 03:19 PM
#17
Thread Starter
Fanatic Member
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
Last edited by Mustaphi; Jun 9th, 2018 at 05:51 PM.
-
Jun 9th, 2018, 11:38 PM
#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
-
Jun 10th, 2018, 06:09 AM
#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).
-
Jun 10th, 2018, 06:54 AM
#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.
If you don't know where you're going, any road will take you there...
My VB6 love-children: Vee-Hive and Vee-Launcher
-
Jun 10th, 2018, 09:10 AM
#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
Last edited by Schmidt; Jun 10th, 2018 at 09:17 AM.
-
Jun 11th, 2018, 04:15 PM
#22
Thread Starter
Fanatic Member
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
-
Jun 11th, 2018, 10:02 PM
#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)
-
Jun 11th, 2018, 11:18 PM
#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
-
Jun 12th, 2018, 05:33 AM
#25
Thread Starter
Fanatic Member
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
-
Jun 16th, 2018, 05:03 AM
#26
Thread Starter
Fanatic Member
-
Jun 16th, 2018, 07:18 AM
#27
Re: How to use SUM() and Group By?
You should have a payments table that relates to Orders. Do you have that?
If you don't know where you're going, any road will take you there...
My VB6 love-children: Vee-Hive and Vee-Launcher
-
Jun 16th, 2018, 07:30 AM
#28
Thread Starter
Fanatic Member
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?
-
Jun 16th, 2018, 07:38 AM
#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.
If you don't know where you're going, any road will take you there...
My VB6 love-children: Vee-Hive and Vee-Launcher
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|