Web Analytics Made Easy -
StatCounter SQL Query Question - CodingForum

Announcement

Collapse
No announcement yet.

SQL Query Question

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQL Query Question

    Code:
    SELECT sum06clients.name,SUM(bikoorim.nights) as totalnights FROM bikoorim INNER JOIN sum06clients on bikoorim.clientID = sum06clients.id GROUP BY bikoorim.clientID ORDER BY totalnights
    Suppose to do this:
    "Select the [name] field from the customers table, and the sum of all the nights of this customer (by clientID) - call the sum of the nights: 'totalnights'.
    Since the customers table and the visits (bikoorim) tables are seperated, join them by the connection: 'bikoorim.clientID = sum06clients.id'.
    Organize all this sh*t by totalnights"

    Too bad IIS can't understand MY English...


    Can someone please help?

    PS
    the error is:
    Code:
    You tried to execute a query that does not include the specified expression 'name' as part of an aggregate function

  • #2
    you have to group on name also:
    SELECT sum06clients.name, SUM(bikoorim.nights) as totalnights
    FROM bikoorim
    INNER JOIN sum06clients on bikoorim.clientID = sum06clients.id
    GROUP BY bikoorim.clientID, sum06clients.name
    ORDER BY totalnights
    I am the luckiest man in the world

    Comment


    • #3
      Thanks!

      Code:
      "SELECT sum06clients.name, SUM(bikoorim.nights) as totalnights FROM bikoorim INNER JOIN sum06clients on bikoorim.clientID = sum06clients.id GROUP BY bikoorim.clientID, sum06clients.name"
      Is working fine
      the problem is when I add:"ORDER BY totalnights" at the end,
      I get:
      "Too few parameters. Expected 1."

      It seems like totalnights doesn't "exist"...
      What can I do?

      Comment


      • #4
        Code:
        "SELECT sum06clients.name, SUM(bikoorim.nights) as totalnights FROM bikoorim INNER JOIN sum06clients on bikoorim.clientID = sum06clients.id GROUP BY bikoorim.clientID, sum06clients.name ORDER BY SUM(bikoorim.nights) desc"
        Works fine...

        What do you think? Is it ok?

        Comment


        • #5
          You tried to execute a query that does not include the specified expression 'depart'

          Hi I have the same error message, when I try to find the flights that have the same destination and depart countries, and income from those flights.

          My SQL syntax is:

          SELECT [FlightNo], [Depart], [Destination], SUM(NumSeats*SeatPrice) AS INCOME
          FROM Flight INNER JOIN Booking on Flight.FlightNo = Booking.Flight
          WHERE [Flight].[Depart]=[Flight].[Destination]
          GROUP BY [Flight].[FlightNo]
          ORDER BY [Flight].[FlightNo], [Flight].[Depart];

          And my table structures are:

          Table Booking has Table Flight has
          BookingNo FlightNo
          Customer Depart
          Flight Destination
          NumSeats Plane
          Seat Price DepDate
          StudentNo FCostFixed
          FCostPassenger

          If anyone have time and knowledge, your help'd be much appreciated.
          regards,
          hulan


          PS
          the error is:
          Code:
          You tried to execute a query that does not include the specified expression 'depart' as part of an aggregate function
          [/QUOTE]

          Comment


          • #6
            You tried to execute a query that does not include the specified expression 'depart'

            Oops, sorry if i confused you, the table structures are:

            Table Booking has
            BookingNo
            Customer
            Flight
            NumSeats
            Seat Price StudentNo

            Table Flight Has

            FlightNo
            Depart
            Destination
            Plane
            DepDate
            FCostFixed
            FCostPassenger



            regards,
            hulan

            PS
            the error is:
            Code:
            You tried to execute a query that does not include the specified expression 'depart' as part of an aggregate function
            [/QUOTE]
            Flight
            NumSeats
            Seat Price
            StudentNo


            If anyone have time and knowledge, your help'd be much appreciated.
            regards,
            hulan


            PS
            the error is:
            Code:
            You tried to execute a query that does not include the specified expression 'depart' as part of an aggregate function
            [/QUOTE]

            Comment


            • #7
              The basic problem is exactly the same as the previous one but since you didn't understand what was wrong (and nobody explained it) you're stuck wondering too.

              The key to getting rid of this error is to know that all the data fields except the one in the "Sum" that you select in your select statement must also be included in the "Group by" list.

              Specifically, you need to add the "Depart" and "Destination" fields to your "Group by" list.
              Check out the Forum Search. It's the short path to getting great results from this forum.

              Comment


              • #8
                The search key was not found in any record

                Originally posted by Roy Sinclair
                The basic problem is exactly the same as the previous one but since you didn't understand what was wrong (and nobody explained it) you're stuck wondering too.

                The key to getting rid of this error is to know that all the data fields except the one in the "Sum" that you select in your select statement must also be included in the "Group by" list.

                Specifically, you need to add the "Depart" and "Destination" fields to your "Group by" list.
                Thank you Roy Sinclair, I tried it with Group By except those fields included in the Sum, however now I get error message like

                "The search key was not found in any record"

                and the query was

                SELECT [Flight].[FlightNo], [Flight].[Depart], [Flight].[Destination], [NumSeats], SUM([NumSeats]*[SeatPrice]) AS TotalSales
                FROM Flight LEFT JOIN Booking ON [Flight].[FlightNo]=[Booking].[Flight]
                WHERE Flight.FlightNo NOT IN (Select Flight FROM Booking)
                GROUP BY [Flight].[FlightNo], [Flight].[Depart], [Flight].[Destination]
                HAVING [Flight].[Depart] = [Flight].[Destination];


                regards,
                hulan

                Comment


                • #9
                  The links I find discussing that particular error message indicate several different possibilities. Here are some links to discussions that may offer you a solution:

                  http://www.tek-tips.com/gviewthread....181/qid/405936
                  http://www.access-programmers.co.uk/...018#post276018
                  http://www.utteraccess.com/forums/ac...ess383899.html
                  http://www.webfrustration.com/archiv...04/01/4/224935
                  Check out the Forum Search. It's the short path to getting great results from this forum.

                  Comment


                  • #10
                    Your condition shoul also not be place in a havin-clause. It should be moved to the where-clause + ne of these fields (destination or departure) should be removed from the fieldlist since the values of both will be the same.
                    I also wonder if you realy need the outer-join...

                    What exactly are you trying to acchieve. I don't see the link between your initial post and the pseuocode inthere, and your query.
                    To get the number of flight per person, you only need

                    select count(*) as numberofflights, customer from booking group by customer order by numberofflights desc
                    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

                    Comment

                    Working...
                    X