5 Replies Latest reply: Dec 12, 2012 10:36 AM by Filip Larsson RSS

    Problem with GROUP BY

    Filip Larsson

      Hi,

       

      I have some problem with the GROUP BY clause when loading a script.

       

      Table:

      LOAD

          Week,

           Status,

          sum(Amount) AS Sales

          WHERE Week > 10;

      SQL SELECT *

      FROM DatabaseName.dbo.TableName GROUP BY Week, Status ORDER BY Week;

       

      My error message is that the first column I select (*) is not grouped by or aggregated, as I don't want to load it into my table. On the other hand, if I do...

      SELECT Week, Status, Amount

      ... I won't accomplish this either.

       

      Thanks for your help in advance!

       

      Best,
      Filip

        • Re: Problem with GROUP BY
          Or Shoham

          Filip,

          This is strictly an SQL problem. You can't select any fields in a GROUP BY query unless they are either part of the GROUP BY list, or aggregated in some function or another (e.g. sum, count, etc).

           

          In your case, you'd probably want to use SELECT Week, Status, sum(Amount).

           

          Alternatively, you can load the full data (without a GROUP BY clause) and group it in QV's load statement, but you'll still have to aggregate Amount.

          • Re: Problem with GROUP BY
            Logesh Jayaraman

            Hi Filip,

             

            You have misplaced the group by wrongly. Your script should be  like this

             

            Table:

            LOAD

                Week,

                 Status,

                sum(Amount) AS Sales

                WHERE Week > 10 group by Week, Status;

             

            SQL SELECT *

            FROM DatabaseName.dbo.TableName ORDER BY Week