Results 1 to 29 of 29

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

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2016
    Posts
    753

    How to use SUM() and Group By?

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

    Name:  pp2.png
Views: 666
Size:  10.4 KB

    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. #2
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    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.

  3. #3

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2016
    Posts
    753

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

    Quote Originally Posted by DataMiser View Post
    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. #4
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

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

    Quote Originally Posted by DataMiser View Post
    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

  5. #5
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

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

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

    Name:  pp2.png
Views: 666
Size:  10.4 KB

    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:
    1. SELECT SUM(Reste) AS SumReste FROM
    2. (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

  6. #6
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,145

    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. #7
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

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

    Quote Originally Posted by SamOscarBrown View Post
    *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

  8. #8
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    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. #9
    gibra
    Guest

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

    Quote Originally Posted by Mustaphi View Post
    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. #10

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2016
    Posts
    753

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

    Quote Originally Posted by Zvoni View Post
    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. #11

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2016
    Posts
    753

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

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

    SQL Code:
    1. SELECT SUM(Reste) AS SumReste FROM
    2. (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. #12

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2016
    Posts
    753

    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. #13

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2016
    Posts
    753

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

    Now suppose I want to calculate the unpaid sum for each record.
    Name:  pp2.png
Views: 507
Size:  10.4 KB
    For example Record 1 : the unpaid sum is 10
    Record 2 : the unpaid sum is 25
    How would be the query please?

  14. #14
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    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

  15. #15
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

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

    Quote Originally Posted by DataMiser View Post
    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

  16. #16

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2016
    Posts
    753

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

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

  17. #17

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2016
    Posts
    753

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

    Quote Originally Posted by SamOscarBrown View Post
    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.

  18. #18
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

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

    Quote Originally Posted by Mustaphi View Post
    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. #19
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,145

    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. #20
    Frenzied Member
    Join Date
    Apr 2012
    Posts
    1,253

    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

  21. #21
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

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

    Quote Originally Posted by ColinE66 View Post
    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.

  22. #22

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2016
    Posts
    753

    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. #23
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    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. #24
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    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

    Quote Originally Posted by Mustaphi View Post
    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. #25

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2016
    Posts
    753

    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. #26

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2016
    Posts
    753

    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

    Name:  tbl1.png
Views: 369
Size:  4.6 KB

    2- Laptop

    Name:  tbl2.png
Views: 430
Size:  3.3 KB

    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 :

    Name:  tbl3.png
Views: 371
Size:  2.3 KB

    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
    Last edited by Mustaphi; Jun 16th, 2018 at 05:22 AM.

  27. #27
    Frenzied Member
    Join Date
    Apr 2012
    Posts
    1,253

    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

  28. #28

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2016
    Posts
    753

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

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

  29. #29
    Frenzied Member
    Join Date
    Apr 2012
    Posts
    1,253

    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
  •  



Click Here to Expand Forum to Full Width