Page 1 of 2 12 LastLast
Results 1 to 40 of 47

Thread: [RESOLVED] join three tables to get Sum

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2016
    Posts
    753

    Resolved [RESOLVED] join three tables to get Sum

    Hello VbForums
    These are approximately my tables:

    Persons_Tbl
    ID............
    1 ..............
    2...............
    3................

    Table MTbl
    PersonID...................Tarif
    1 ...........................10
    1............................50
    2............................20

    Table Act_tbl
    PersonID...............Recette
    1...........................30
    3..........................40

    Code:
    StrSql = "SELECT TarifTotal + RecetteTotal AS GrandTotal " & _
    " FROM Persons_Tbl INNER JOIN ( " & _
           "    SELECT PersonID, SUM(Recette) As TarifTotal " & _
            "   From MTbl" & _
             " GROUP BY PersonID) AS T1 ON T1.PersonID = Persons_Tbl.ID INNER JOIN ( " & _
                "    SELECT PersonID, SUM(Tarif) As RecetteTotal " & _
                  "    From Acte_tbl " & _
                    "   GROUP BY PersonID) AS T2 ON T2.PersonID  = T1.PersonID where T1.PersonID= 1"
    This query is working fine
    However
    Code:
    StrSql = "SELECT TarifTotal + RecetteTotal AS GrandTotal " & _
    " FROM Persons_Tbl INNER JOIN ( " & _
           "    SELECT PersonID, SUM(Recette) As TarifTotal " & _
            "   From MTbl" & _
             " GROUP BY PersonID) AS T1 ON T1.PersonID = Persons_Tbl.ID INNER JOIN ( " & _
                "    SELECT PersonID, SUM(Tarif) As RecetteTotal " & _
                  "    From Acte_tbl " & _
                    "   GROUP BY PersonID) AS T2 ON T2.PersonID  = T1.PersonID where T1.PersonID= 2"
    This query is throwing error because Record 2 has no reference in Act_tbl though it has reference in Persons_Tbl

    Thank you everyone

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

    Re: join three tables to get Sum

    LEFT JOIN instead of INNER JOIN?
    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

  3. #3

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2016
    Posts
    753

    Re: join three tables to get Sum

    Quote Originally Posted by Zvoni View Post
    LEFT JOIN instead of INNER JOIN?
    Even Left Join is throwing error: Either Eof or Bof is true
    thank you

  4. #4
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: join three tables to get Sum

    LEft join is what you want... but you need to handle NULL values.... when you add NULL vlaues, the whole expression becomes null...
    So when you get to this point: SELECT TarifTotal + RecetteTotal AS GrandTotal for record 2, you have 20 + NULL, which results in a NULL ... so you need to wrap your TariffTotal and RecetteTotals with NZ or ISNULL (or what ever the is null function is for the db you're using, the syntax suggests Access, which if I remember is NZ())

    Personally, it's not how I'd write the query, but eh... it's just me.


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2016
    Posts
    753

    Re: join three tables to get Sum

    Currently I'm using Sqlite3 and RC6
    I added:
    Code:
    where ifnull(GrandTotal, '') And T1.PersonID= 2
    I even used NT function but still the same error
    Personally, it's not how I'd write the query
    would you please suggest another query?
    thank you

  6. #6
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: join three tables to get Sum

    Hmmm... I see the problem -- it became apparent when I tried to re-write it...
    Code:
    "   GROUP BY PersonID) AS T2 ON T2.PersonID  = T1.PersonID where T1.PersonID= 2"
    Dont join your second table to your thrid... you should be joining frmo your main table Person_tbl.... and your where should be on the main table as well, not on the joined tables.

    Code:
    "   GROUP BY PersonID) AS T2 ON T2.PersonID = Persons_Tbl.ID where Persons_Tbl.ID= 2"

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  7. #7
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: join three tables to get Sum

    @tg got that so right.

    Basic rule of thumb when dealing with child tables is to make the MAIN table - the FROM table - be the parent table.

    If that can't be done and you still need a "complete" population of ID's from two child tables, then do just that - make a derived query to return a DISTINCT list of ID's from two union'ed SELECT's. That's like the only time I advocate for the DISTINCT keyword (which is generally evil in the hands of a novice SQL writer).

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  8. #8

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2016
    Posts
    753

    Re: join three tables to get Sum

    Code:
    "   GROUP BY PersonID) AS T2 ON T2.PersonID = Persons_Tbl.ID where Persons_Tbl.ID= 2"
    I was able to avoid the error
    but when I try to print the output of the query it fails.
    Code:
    Debug.Print Rs!GrandTotal
    I tried with :
    Persons_Tbl.ID= 2
    Persons_Tbl.ID= 3
    Persons_Tbl.ID= 4
    It only prints the output when the record has reference in both MTbl and Acte_tbl
    thank you

  9. #9

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2016
    Posts
    753

    Re: join three tables to get Sum

    Quote Originally Posted by szlamany View Post
    @tg got that so right.

    Basic rule of thumb when dealing with child tables is to make the MAIN table - the FROM table - be the parent table.

    If that can't be done and you still need a "complete" population of ID's from two child tables, then do just that - make a derived query to return a DISTINCT list of ID's from two union'ed SELECT's. That's like the only time I advocate for the DISTINCT keyword (which is generally evil in the hands of a novice SQL writer).
    What you suggest seems a bit complicated for a novice .
    Would you please provide more simplicity?
    thanks

  10. #10
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: join three tables to get Sum

    Would be this in MS-SQL

    Code:
    Select PT.PersonID
        ,(Select Sum(MT.Tarif) From MTbl MT Where MT.PersonID=PT.PersonID) "TarifTotal"
        ,(Select Sum(AT.Recette) From Act_Tbl AT Where AT.PersonID=PT.PersonID) "RecetteTotal"
        From Persons_Tbl PT
    Does that work?

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  11. #11

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2016
    Posts
    753

    Re: join three tables to get Sum

    Thank you
    I find it hard to put the query into practice.
    I'm having sytax error
    Are "TarifTotal" and "RecetteTotal" part of the code?
    thank you

  12. #12
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: join three tables to get Sum

    Change it to as TarifTotal and as RecetteTotal

    I'm just naming the columns - remove it all together.

    Are you MS-SQL?

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  13. #13

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2016
    Posts
    753

    Re: join three tables to get Sum

    I'm using SQlite and RC6
    Code:
    StrSql = "Select PT.PersonID " & _
        "(Select Sum(MT.Tarif) From MTbl MT Where MT.PersonID = PT.PersonID) as TarifTotal" & _
        "(Select Sum(AT.Recette) From Act_Tbl AT Where AT.PersonID = PT.PersonID) as RecetteTotal" & _
        " From Persons_Tbl PT"
    Code:
    StrSql = "Select PT.PersonID " & _
        "(Select Sum(MT.Tarif) From MTbl MT Where MT.PersonID = PT.PersonID)" & _
        "(Select Sum(AT.Recette) From Act_Tbl AT Where AT.PersonID = PT.PersonID)" & _
        " From Persons_Tbl PT"
    NO luck.
    I'm having syntax error near "(" but I can't figure it out
    thanks

  14. #14
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: join three tables to get Sum

    Missing commas in front of each sub-query.

    Code:
    StrSql = "Select PT.PersonID " & _
        ",(Select Sum(MT.Tarif) From MTbl MT Where MT.PersonID = PT.PersonID) as TarifTotal" & _
        ",(Select Sum(AT.Recette) From Act_Tbl AT Where AT.PersonID = PT.PersonID) as RecetteTotal" & _
        " From Persons_Tbl PT"

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  15. #15

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2016
    Posts
    753

    Re: join three tables to get Sum

    Thank you
    finally the code is responding without errors
    Last edited by Mustaphi; Feb 12th, 2021 at 06:02 PM.

  16. #16
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: join three tables to get Sum

    Instead of having a comma in front of the second sub-query, make that a + sign, and you are adding them together. Make it a minus sign and you are substracting.

    I showed you how to get a subquery to return the value for that child table - those two child tables in separate columns.

    You can do whatever you want to do with them.

    Otherwise, I don't understand your needs...sorry...

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  17. #17

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2016
    Posts
    753

    Re: join three tables to get Sum

    I'm sorry I was adding
    Code:
    where PT.ID = 1
    I didn't pay attention
    I apologize

  18. #18

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2016
    Posts
    753

    Re: join three tables to get Sum

    Now I need to select another field MyDate

    Code:
    StrSql = "Select MT.MyDate, PT.PersonID " & _
        ",(Select Sum(MT.Tarif) From MTbl MT Where MT.PersonID = PT.PersonID) as TarifTotal" & _
        ",(Select Sum(AT.Recette) From Act_Tbl AT Where AT.PersonID = PT.PersonID) as RecetteTotal" & _
        " From Persons_Tbl PT"
    Code:
    StrSql = "Select  PT.PersonID " & _
        ",(Select MT.MyDate, Sum(MT.Tarif) From MTbl MT Where MT.PersonID = PT.PersonID) as TarifTotal" & _
        ",(Select Sum(AT.Recette) From Act_Tbl AT Where AT.PersonID = PT.PersonID) as RecetteTotal" & _
        " From Persons_Tbl PT"
    But each time I get message no such field "MyDate"
    Thank you

  19. #19
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: join three tables to get Sum

    There is not a single MyDate for an ID so which would you want?

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  20. #20

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2016
    Posts
    753

    Re: join three tables to get Sum

    I have MyDate in both child tables
    I want to use the one of MTbl table.
    I think MyDAte in the where clause which is not reognized

  21. #21
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: join three tables to get Sum

    Sub-queries like the one I showed you - those that are inline in place of where you would put a single field in a statement - they can only return a single field themselves. And of course a single row. They have to fit into the constraints of the domain you are pulling them into.

    Business needs as expressed by you so far:

    1. Create a SINGLE ROW for each PersonID.
    2. Use sub-queries to aggregate multiple rows for a PersonID, from those child tables, into a single entity through the SUM() function.


    With that said, you are now introducing a new fields - MyDate. You haven't shown sample data with that column, making us have to guess at what type of values appear.

    You ask for that MyDate to show on the single row we are producing for a PersonID.

    Continuing on with guessing how this really looks, you probably have "different dates" for each row in the two child tables.

    Making us scratch our heads as to which date you want to focus on while trying to wrestle it into a single entity to fit on the one row you have for a PersonID. The MAX()? The MIN()?

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  22. #22

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2016
    Posts
    753

    Re: join three tables to get Sum

    I have put your last post into google translate and read it many times trying to understand the idea.
    Sincerely I cannot understand everything.
    What I'm trying to do is this:
    Code:
    StrSql = "Select PT.PersonID " & _
        ",(Select MT.MyDate, Sum(MT.Tarif) From MTbl MT Where MT.PersonID = PT.PersonID) as TarifTotal" & _
        ",(Select Sum(AT.Recette) From Act_Tbl AT Where AT.PersonID = PT.PersonID) as RecetteTotal" & _
        " From Persons_Tbl PT where Date(MyDate) = '" & Format(Date, "yyyy-mm-dd") & "'""
    (MyDate) in the where clause is not recognized.

    Thanks

  23. #23
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: join three tables to get Sum

    This

    Code:
    (Select MT.MyDate, Sum(MT.Tarif) From MTbl MT Where MT.PersonID = PT.PersonID) as TarifTotal" &
    Can only return one field

    MT.MyDate is not allowed in that sub-query.

    For two reasons. And each is important to understand.

    You have several rows in the MTbl table for each PersonID. Which date would you want - from which row? You can use MAX() or MIN() to get just one date from multiple rows.

    That is the first reason.

    The second reason is more important.

    This sub-query is going into a spot in the SELECT statement that only allows a single field to appear.

    In order for us to fix this and move forward you must first tell me which date you want to use when there is several different dates for a PersonID in the MTbl table.

    If we are still having translate problems, please show me some sample data with dates so I can work up a solution.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  24. #24

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2016
    Posts
    753

    Re: join three tables to get Sum

    Thank you sir
    So as far as my poor understanding is concerned, it is very difficult to deal with the following situation.

    Persons_Tbl
    ID............
    1 ..............
    2...............
    3................

    Table MTbl
    PersonID...................Tarif ......................MyDate
    1 ...........................10 .....................2021-02-13
    1............................50 ......................2021-01-01
    2............................20 ....................2021-02-13

    Table Act_tbl
    PersonID...............Recette ......................MyDate
    1...........................30 .....................2020-02-11
    3..........................40 .....................2021-02-15

    Code:
    StrSql = "Select PT.PersonID " & _
        ",(Select MT.MyDate, Sum(MT.Tarif) From MTbl MT Where MT.PersonID = PT.PersonID) as TarifTotal" & _
        ",(Select Sum(AT.Recette) From Act_Tbl AT Where AT.PersonID = PT.PersonID) as RecetteTotal" & _
        " From Persons_Tbl PT where Date(MyDate) = '" & Format(Date, "yyyy-mm-dd") & "'""
    The output of this query should be:
    1 ...........................10 .....................2021-02-13
    2............................20 ....................2021-02-13

  25. #25
    PowerPoster
    Join Date
    Nov 2017
    Posts
    3,116

    Re: join three tables to get Sum

    Just a heads up - this is likely a carryover from this other thread:

    https://www.vbforums.com/showthread....rom-two-tables

    where, in that thread, the OP also continued to introduce new details. So this is likely to turn into another "Columbo" thread where once the issue is solved, "one more thing" will be introduced.

  26. #26
    PowerPoster
    Join Date
    Nov 2017
    Posts
    3,116

    Re: join three tables to get Sum

    As I pointed out in your other similar thread, this is exactly why you need to specify your exact needs in the first post. The number of people willing to help you will decrease significantly if you continue to post a problem, people spend their own time trying to help you solve it, and then you add new details on what exactly you need to do.

    Good luck.

  27. #27

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2016
    Posts
    753

    Re: join three tables to get Sum

    As I pointed out in your other similar thread, this is exactly why you need to specify your exact needs in the first post.
    My initial problem was to sum values in multiple tables.
    But I did not know that this will result in new problems in selecting new fields.
    New problems appear while testing the codes provided by experts.

  28. #28
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: join three tables to get Sum

    @optionbase1 - thanks for the warning. I'll spend a bit more time here to see if I can help.

    @mustaphi - this is exactly why a VIEW is a good idea on a child table. Look at this VIEW.

    Code:
    Create View MTbl_Person_View
    as
    Select PersonID
                     ,Sum(Tarif) "TotalTarif"
                     ,Max(MyDate) "MaxDate"
                     ,Min(MyDate) "MinDate"
                     ,Sum(1) "NumberOfTransactions"
           From MTbl
           Group by PersonID
    Now you have a VIEW that has ONE row for each PersonID and along with that a bunch of fields related to SUM() and MAX() and MIN() and even a record count.

    Join to this VIEW instead of the table and you should have what you need.

    Make a similar VIEW of the other table.
    Last edited by szlamany; Feb 13th, 2021 at 10:41 AM.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  29. #29

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2016
    Posts
    753

    Re: join three tables to get Sum

    Make a similar VIEW of the other table
    Code:
    Create View Act_Person_View
    as
    Select PersonID
                     ,Sum(Recette) "TotalRecette"
                     ,Max(MyDate) "MaxDate"
                     ,Min(MyDate) "MinDate"
                     ,Sum(1) "NumberOfTransactions"
           From Act_Tbl 
           Group by PersonID
    Easy to do this view but what then?
    What's the use of the aggregates : Max(MyDate) and Min(MyDate)
    As far as I know they are used to calculate the biggest and smallest dates in a field.
    thanks

  30. #30
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: join three tables to get Sum

    Does the date that you are selecting only exist one time in MTbl?

    If it does not exist, do you want to show that PersonID at all?

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  31. #31

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2016
    Posts
    753

    Re: join three tables to get Sum

    Persons_Tbl
    ID............
    1 ..............
    2...............
    3................

    Table MTbl
    PersonID...................Tarif ......................MyDate
    1 ...........................10 .....................2021-02-13
    1............................50 ......................2021-01-01
    2............................20 ....................2021-02-13

    Table Act_tbl
    PersonID...............Recette ......................MyDate
    1...........................30 .....................2020-02-11
    3..........................40 .....................2021-02-15
    3..........................15 .....................2021-02-13

    In a situation similar to the above:

    I need a query that loops all records and sort them based on MyDate : ( where MyDate = Date)
    1 ...........................10 .....................2021-02-13
    2............................20 ....................2021-02-13
    3..........................15 ......................2021-02-13

    Then another one that sums the values based on Mydate.
    In this scenario: the sum is 45
    Anyway if you think it is complicated, I will abandon the idea with many thanks for your help.

  32. #32
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: join three tables to get Sum

    That is achieved with a different method.

    What you are showing me now is that MTbl and Act_tbl are treated the same. The Tarif value and Recette values are simply added up for a date.

    That is done with a UNION ALL of both tables

    Code:
    Select PersonID, Tarif, MyDate from MTbl
    Union All
    Select PersonID, Recette, MyDate from Act_Tbl
    If you were to run that query you would get this result

    PersonID...................Tarif ......................MyDate
    1 ...........................10 ......................2021-02-13
    1............................50 ......................2021-01-01
    1............................30 ......................2020-02-11
    2............................20 ......................2021-02-13
    3............................40 ......................2021-02-15
    3............................15 ......................2021-02-13

    I only showed you that so you would have understanding of what UNION ALL does.

    You get the result you want by taking that UNION and doing this with it.

    Code:
    Select AAA.PersonID, Sum(AAA.Tarif), AAA.MyDate From
              (Select PersonID, Tarif, MyDate from MTbl
              Union All
              Select PersonID, Recette, MyDate from Act_Tbl) as AAA
        Group by AAA.PersonID, AAA.MyDate
    Last edited by szlamany; Feb 13th, 2021 at 12:37 PM.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  33. #33

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2016
    Posts
    753

    Re: join three tables to get Sum

    Million thanks
    I learnt so many things that I was ignoring or I just know little about.
    So far things seem to work properly but I need some time to test and re-test
    then I will close the thread.
    thanks a lot

  34. #34
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: join three tables to get Sum

    You are very welcome!

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  35. #35

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2016
    Posts
    753

    Re: join three tables to get Sum

    Me again
    Code:
    StrSql = "Select AAA.PersonID, Sum(AAA.Tarif), AAA.MyDate From " & _
              " (Select PersonID, Tarif, MyDate from MTbl " & _
             " Union All " & _
             " Select PersonID,  Sum(Recette) as GrandRecette, MyDate from Act_tbl) as AAA " & _
       " Group by AAA.PersonID, AAA.MyDate "
    I want to sum the fields Tarif ad Recette
    It's OK with Tarif but no success with Recette.
    I get error "No such field Recette.
    Last edited by Mustaphi; Feb 13th, 2021 at 02:08 PM.

  36. #36
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: join three tables to get Sum

    Show me what you are trying to do with data

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  37. #37
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: join three tables to get Sum

    I believe what you are trying to tell me, is that, if there are two or more on the same DATE for a PersonID, you want the SUM that activity.

    Please confirm this.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  38. #38

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2016
    Posts
    753

    Re: join three tables to get Sum

    Show me what you are trying to do with data
    Table MTbl
    PersonID...................Tarif ......................MyDate
    1 ...........................10 .....................2021-02-13
    1............................50 ......................2021-01-01
    2............................20 ....................2021-02-13

    Table Act_tbl
    PersonID...............Recette ......................MyDate
    1...........................30 .....................2020-02-11
    3..........................40 .....................2021-02-15
    3..........................15 .....................2021-02-13

    In a situation similar to the above:
    Sum(Tarif) = 80
    Sum(Recette) = 85
    In this query
    Code:
    StrSql = "Select AAA.PersonID, Sum(AAA.Tarif) as GrandTarif, AAA.MyDate From " & _
              " (Select PersonID, Tarif, MyDate from MTbl " & _
             " Union All " & _
             " Select PersonID,  Sum(Recette) as GrandRecette, MyDate from Act_tbl) as AAA "
    If I print RS! GrandTarif it print 85
    but trying to print RS!GrandRecette give error No such field

  39. #39
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: join three tables to get Sum

    Try this instead

    Code:
    Select PersonID, Tarif, Cast(0 as money) "Recette", MyDate from MTbl
    Union All
    Select PersonID, Cast(0 as money), Recette, MyDate from Act_Tbl
    Which will allow you to do this instead

    Code:
    Select AAA.PersonID, Sum(AAA.Tarif), Sum(AAA.Recette), AAA.MyDate From
              (Select PersonID, Tarif, Cast(0 as money) "Recette", MyDate from MTbl
              Union All
              Select PersonID, Cast(0 as money), Recette, MyDate from Act_Tbl) as AAA
        Group by AAA.PersonID, AAA.MyDate

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  40. #40

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2016
    Posts
    753

    Re: join three tables to get Sum

    Thank you sir
    Things start to get complicated and I think it's time to surrender.
    But before doing that would you please tell me what is Cast(0 as money)?
    And what is it doing here in the query?

    Code:
    StrSql = "Select PersonID, Tarif, Cast(0 as money), MyDate from MTbl" & _
             " Union All " & _
             " Select PersonID, Cast(0 as money), Sum(Recette) as GrandRecette, MyDate from Act_Tbl "
    trying to print RS!GrandRecette give error No such field

Page 1 of 2 12 LastLast

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