1 2 Previous Next 15 Replies Latest reply on May 1, 2014 5:49 PM by beverly

    SQL help

    kaostika

      I need to show the records like this, but I am not sure how to put in the SUM into this. I want to get a SUM of the last Column


      Captain,2,2

      Waiter,3,3

      Bartender,2,2

      Head Chef,1,2

      Coat Check,6,1



      This is what I have so far.


      ExecuteSQL ( "




      SELECT O.Role , R.Recommended , (A.ONE)

      FROM R.Recommended AS R

      JOIN Assigned AS A ON A.IDrole = R.IDrole

      JOIN Roles AS O ON R.IDrole= O.IDrole

      WHERE R.IDevent =?


      ORDER BY O.Sort



      " ; "" ; "" ; Events::IDevent)


      It generates This. I need to combine the similar job into one line with the Second column and the Sum of the Last


      Captain,2,1

      Captain,2,1

      Waiter,3,1

      Waiter,3,1

      Waiter,3,1

      Bartender,2,1

      Bartender,2,1

      Head Chef,1,1

      Head Chef,1,1

      Coat Check,6,1


      Oreste

        • 1. Re: SQL help
          jbrown

          HI there.

          I think the SUM is missing from the last field you selected??

          SELECT O.Role, R.Recommended, SUM(A.One) . . .

          • 2. Re: SQL help
            kaostika

            Yes, this gets a ?

             

             

             

            ExecuteSQL ( "

             

             

             

            SELECT   (O.Role) , R.Recommended , SUM (A.ONE)

            FROM Assigned AS A

            JOIN Recommended AS R ON A.IDrole = R.IDrole

            JOIN Roles AS O ON R.IDrole= O.IDrole

             

             

            ORDER BY O.Sort

             

             

             

             

            " ; "" ; "" ; Events::IDevent)

            • 3. Re: SQL help
              jbrown

              Sorry I meant to finish my reply

               

              Since you're doing a Sum, you need to put in a GROUP By Clause, and group by any non-aggregated field in your SELECT Clause

               

              So I think your GROUP BY would be:


              GROUP BY O.Role,R.Recommended

               

              This would go before the ORDER BY Clause.

              You want to get a sum of all the Captain roles, right? So The Group By needs to be in there.

              • 4. Re: SQL help
                kaostika

                Generate a ?  does it matter that some are Numbers?

                 

                ExecuteSQL ( "

                 

                 

                SELECT   (O.Role) , R.Recommended , SUM (A.ONE)

                FROM Assigned AS A

                JOIN Recommended AS R ON  R.IDrole = A.IDrole

                JOIN Roles AS O ON R.IDrole= O.IDrole

                GROUP BY O.Role

                ORDER BY O.Sort

                 

                 

                 

                 

                " ; "" ; "" ; Events::IDevent)

                • 5. Re: SQL help
                  jbrown

                  Nope. It doesn't matter if some of the SELECT fields are numbers. Those will not be SUMmed. They'll just be put next to the Role.

                   

                  If records that had the role of Captain had differing Recommended values, then each differing value would cause a new Group to be summed. Thus you'd get this in the ExecuteSQL return:

                   

                  Captain, 1, 4

                  Captain, 2, 2

                  . Does that make sense?

                   

                  Use this GROUP BY O.Role, R.Recommended

                   

                  That will work. All non-aggregated fields in the SELECT must be in the GROUP BY

                  • 6. Re: SQL help
                    kaostika

                    Does it matter if there isnt a record in the Assigned table?  Sometimes that will be empty. 

                    • 7. Re: SQL help
                      jbrown

                      HMM. Smarter people than me may have the answer. I can speculate.

                       

                      I think, since your FROM clause is from the Assigned Table, you won't get a Role or Recommended from anything that is NOT in your assigned table.

                       

                      If you were to do the FROM from the ROLE table, you MIGHT get 0 if there were no Assigned records related to a role that is in the role table.

                       

                      Beverly ?

                      • 8. Re: SQL help
                        kaostika

                        It seems to Work if the Sum is in the same table as the Main

                        • 9. Re: SQL help
                          kaostika

                          This is a beverly problem lol

                          • 10. Re: SQL help
                            kaostika

                            This Works.  The only Issue is that I will have more recommended than assigned so I need to see the Null Values

                             

                            ExecuteSQL

                            ("

                             

                             

                            SELECT  O.Role,R.Recommended, COUNT (A.IDassigned)

                             

                             

                            FROM Assigned AS A

                             

                             

                            INNER JOIN Recommended AS R ON R.IDrole=A.IDROLE

                             

                             

                            INNER JOIN Roles O ON R.IDROLE=O.IDRole

                            WHERE R.IDevent =?

                            GROUP BY  O.role,R.Recommended

                             

                             

                             

                             

                            ";

                            "";""; Events::IDevent

                            )

                            • 11. Re: SQL help
                              jbrown

                              That's awesome. I was just brushing up on my join-differences.

                              • 12. Re: SQL help
                                kaostika

                                This Works Completely.  Must have been something in the Group By that was an issue

                                 

                                ExecuteSQL

                                ("

                                 

                                SELECT  O.Role,R.Recommended, COUNT (A.IDassigned)

                                 

                                FROM  Recommended AS R

                                 

                                LEFT JOIN Assigned AS A ON A.IDrole=R.IDROLE

                                 

                                JOIN Roles O ON O.IDROLE=R.IDRole

                                WHERE R.IDevent =?

                                 

                                GROUP BY  O.sort,O.Role,R.Recommended

                                ORDER BY O.sort

                                ";

                                "";""; Events::IDevent

                                )

                                 

                                 

                                 

                                Captain,2,2

                                 

                                Waiter,3,0

                                Bartender,2,1

                                Head Chef,1,0

                                Chef,5,0

                                Coat Check,6,0

                                Sanit,6,0

                                • 13. Re: SQL help
                                  beverly

                                  the difference is the LEFT JOIN.

                                   

                                  that "finds" any parent whether it has children or not.

                                   

                                  Beverly

                                  • 14. Re: SQL help
                                    jbrown

                                    Beverly. That's what I learned. You must have read the same paper I did: the Missing FM ExecuteSQL Reference. That author had some amazing techniques.

                                    1 2 Previous Next