3 Replies Latest reply on Oct 10, 2012 5:07 AM by beverly

    ExecuteSQL order by aggregate

    carlo.m

      I'm trying to order a sqlquery by an aggregate and having no luck.

       

      The query:

       

      Select Account, SUM(Cost)

      from transactions

      group by account

      order by SUM(Cost)

       

      the query runs fine. But it is not ordered as asked. The thing that throws me off is that it returns a result, no error. I can change the order to DESC and it runs but the order does not change at all.

       

       

      Help!

        • 1. Re: ExecuteSQL order by aggregate
          greglane

          Create a column alias for the aggregate and then reference the alias in the ORDER BY clause.

           

          Select Account, SUM(Cost) AS account_sum

          from transactions

          group by account

          order by account_sum

          • 2. Re: ExecuteSQL order by aggregate
            carlo.m

            Thanks Greg!

             

            Apart from Self Joins, Group by, having and order, anything else you can use aliases for?

             

            Can you agreggate them? (i.e. select sum(a) as s_a, sum(b) as s_b, s_a+s_b as total)

            • 3. Re: ExecuteSQL order by aggregate
              beverly

              You can alias the tableName and/or columnName. For example on table names, those illegal characters or really LONG table occurrance names can be aliased and used with references if needed:

              " SELECT c.firstName, c.\"Last Name\", c.address, c.city, s.stateFull, c.postal, c.country
              FROM \"contacts by contactType_customer\" AS c
                JOIN stateLookup AS s ON c.\"state\" = s.stateAbbrev
              WHERE c.\"state\" = 'IA'
                AND c.contactActive = 1
              ORDER BY c.postal, c.lastName, c.firstName "

              And when using more than one table, it's helpful to alias the tables. You can use any valid values for the alias'

              HTH

              Beverly