Menu

"group by" with multiple items

Help
Brendan
2005-06-24
2012-09-19
  • Brendan

    Brendan - 2005-06-24

    I'm using ActiveState Python 2.3, mysql-python 1.2.0, and MySQL 4.1.

    I'm observing unexpected behaviour when using "group by" with multiple items in a select query:

    import MySQLdb
    db = MySQLdb.connect(host='localhost',user='user',passwd='somepassword',db='somedb')
    Cursor = db.cursor()

    This one doesn't return any records

    sql = "select sevfirst_yr, first_yr_rd from defspraycorr where sevfirst_yr <> 9999 group by sevfirst_yr, first_yr_rd"

    This one does, albeit not the results I want

    sql = "select sdef1974, first_yr_rd from defspraycorr where sevfirst_yr <> 9999 group by sevfirst_yr"

    This one provides the correct results, although I had to add the count(*) where I don't when working directly with MySQL

    sql = "select sevfirst_yr, first_yr_rd, count(*) from defspraycorr where sevfirst_yr <> 9999 group by sevfirst_yr, first_yr_rd"

    Cursor.execute(sql)
    Recs = Cursor.fetchall()
    for record in Recs:
    print record[0], record[1]

    db.close()

    Using count(*) solves my problem, but since it's not required in MySQL, I thought this might be a bug.

    Thanks for the software and hard work.

     
    • Andy Dustman

      Andy Dustman - 2005-06-28

      What happens when you execute those statements in the mysql command-line client?

       
      • Brendan

        Brendan - 2005-06-28

        Sorry, should have included that.

        The first command works as expected, in that it returns every unique combination of those two items, and no more. The python code I provided fails to return anything.

        The second command returns only the unique occurences of the first item, with presumably the first encountered value of the second item. The python code produces the same result.

        The third command returns the same as the first, but with the additional count(*) item. The python code produces the same result.

        Thanks for having a look.

         

Log in to post a comment.

Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.