ML
    • Recent
    • Categories
    • Tags
    • Popular
    • Users
    • Groups
    • Register
    • Login

    Help with SQL query

    Scheduled Pinned Locked Moved Solved IT Discussion
    sqldatabasemysqlmariadb
    24 Posts 4 Posters 4.8k Views
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • scottalanmillerS
      scottalanmiller
      last edited by

      You have caps in your column names? You get a hand slap for that one.

      thegillionT 1 Reply Last reply Reply Quote 0
      • scottalanmillerS
        scottalanmiller @thegillion
        last edited by

        @thegillion said in Help with SQL query:

        SET bathrooms.Rating = (SELECT AVG(Reviews.Rating)

        Pretty sure the issue is here...

        1 Reply Last reply Reply Quote 0
        • thegillionT
          thegillion @scottalanmiller
          last edited by

          @scottalanmiller said in Help with SQL query:

          You have caps in your column names? You get a hand slap for that one.

          I know how to fix that 🙂 I don't think that's the problem with the query

          1 Reply Last reply Reply Quote 0
          • scottalanmillerS
            scottalanmiller
            last edited by

            I don't think that you can use AVG in that way. It doesn't handle the column in that way.

            thegillionT 1 Reply Last reply Reply Quote 0
            • thegillionT
              thegillion @scottalanmiller
              last edited by

              @scottalanmiller I know you can user AVG like this

              SELECT AVG(`Rating`) FROM `Reviews` WHERE `bathroom_ID` = 100
              
              scottalanmillerS 1 Reply Last reply Reply Quote 1
              • scottalanmillerS
                scottalanmiller
                last edited by

                This is MySQL, right?

                thegillionT 1 Reply Last reply Reply Quote 0
                • thegillionT
                  thegillion @scottalanmiller
                  last edited by

                  @scottalanmiller yes

                  1 Reply Last reply Reply Quote 0
                  • scottalanmillerS
                    scottalanmiller @thegillion
                    last edited by

                    @thegillion said in Help with SQL query:

                    @scottalanmiller I know you can user AVG like this

                    SELECT AVG(`Rating`) FROM `Reviews` WHERE `bathroom_ID` = 100
                    

                    Yes, that I know works, too.

                    1 Reply Last reply Reply Quote 0
                    • scottalanmillerS
                      scottalanmiller
                      last edited by

                      Is this an intentional fast lookup cache table? Because this violated the database integrity. Just a thought while I'm looking for an answer.

                      thegillionT 1 Reply Last reply Reply Quote 0
                      • scottalanmillerS
                        scottalanmiller @thegillion
                        last edited by

                        @thegillion said in Help with SQL query:

                        Maybe I'm obtuse, but why is there an AS Rating? Just remove that bit.

                        StrongBadS 1 Reply Last reply Reply Quote 1
                        • scottalanmillerS
                          scottalanmiller
                          last edited by

                          UPDATE bathrooms
                             SET bathrooms.Rating = (SELECT AVG(Reviews.Rating)
                                             FROM Reviews
                                            WHERE Reviews.bathroom_ID = bathrooms.id)
                          
                          thegillionT 1 Reply Last reply Reply Quote 1
                          • scottalanmillerS
                            scottalanmiller
                            last edited by

                            Did that work?

                            1 Reply Last reply Reply Quote 0
                            • StrongBadS
                              StrongBad @scottalanmiller
                              last edited by

                              @scottalanmiller said in Help with SQL query:

                              @thegillion said in Help with SQL query:

                              Maybe I'm obtuse, but why is there an AS Rating? Just remove that bit.

                              I was just looking at that and couldn't see where it was being used either.

                              1 Reply Last reply Reply Quote 0
                              • thegillionT
                                thegillion @scottalanmiller
                                last edited by

                                @scottalanmiller said in Help with SQL query:

                                UPDATE bathrooms
                                   SET bathrooms.Rating = (SELECT AVG(Reviews.Rating)
                                                   FROM Reviews
                                                  WHERE Reviews.bathroom_ID = bathrooms.id)
                                

                                @scottalanmiller said in Help with SQL query:

                                Did that work?

                                Yes it did work thank you!!!

                                scottalanmillerS 1 Reply Last reply Reply Quote 1
                                • thegillionT
                                  thegillion @scottalanmiller
                                  last edited by

                                  @scottalanmiller said in Help with SQL query:

                                  Is this an intentional fast lookup cache table? Because this violated the database integrity. Just a thought while I'm looking for an answer.

                                  The bathroom table is the main table for lookups so yes and the server runs on SSDs with 4gb of ram and two 3.2ghz vCores

                                  scottalanmillerS 1 Reply Last reply Reply Quote 0
                                  • scottalanmillerS
                                    scottalanmiller @thegillion
                                    last edited by

                                    @thegillion said in Help with SQL query:

                                    @scottalanmiller said in Help with SQL query:

                                    UPDATE bathrooms
                                       SET bathrooms.Rating = (SELECT AVG(Reviews.Rating)
                                                       FROM Reviews
                                                      WHERE Reviews.bathroom_ID = bathrooms.id)
                                    

                                    @scottalanmiller said in Help with SQL query:

                                    Did that work?

                                    Yes it did work thank you!!!

                                    Boom, sweet! You can use the ellipses on the individual post to make it as the correct answer, too 🙂

                                    1 Reply Last reply Reply Quote 0
                                    • scottalanmillerS
                                      scottalanmiller @thegillion
                                      last edited by

                                      @thegillion said in Help with SQL query:

                                      @scottalanmiller said in Help with SQL query:

                                      Is this an intentional fast lookup cache table? Because this violated the database integrity. Just a thought while I'm looking for an answer.

                                      The bathroom table is the main table for lookups so yes and the server runs on SSDs with 4gb of ram and two 3.2ghz vCores

                                      Are you going to do a scheduled batch run on this (tee hee, batch run) like at night to update the reviews?

                                      thegillionT 1 Reply Last reply Reply Quote 1
                                      • thegillionT
                                        thegillion @scottalanmiller
                                        last edited by

                                        @scottalanmiller said in Help with SQL query:

                                        Are you going to do a scheduled batch run on this (tee hee, batch run) like at night to update the reviews?

                                        I was going to do that or pass the ID of the bathroom then fire it on a review or bathroom submission.

                                        scottalanmillerS 1 Reply Last reply Reply Quote 1
                                        • scottalanmillerS
                                          scottalanmiller @thegillion
                                          last edited by

                                          @thegillion said in Help with SQL query:

                                          @scottalanmiller said in Help with SQL query:

                                          Are you going to do a scheduled batch run on this (tee hee, batch run) like at night to update the reviews?

                                          I was going to do that or pass the ID of the bathroom then fire it on a review or bathroom submission.

                                          You could use a database trigger for that, too.

                                          1 Reply Last reply Reply Quote 0
                                          • scottalanmillerS
                                            scottalanmiller
                                            last edited by

                                            If you expect this product to explode you might want to consider a NoSQL database now. Easy to switch now, hard later. MySQL scales pretty big, but things like MongoDB and Cassandra go so much bigger. Worth thinking about now before you are more or less locked in.

                                            BRRABillB 1 Reply Last reply Reply Quote 1
                                            • 1
                                            • 2
                                            • 1 / 2
                                            • First post
                                              Last post