SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot DarkMonkey's Avatar
    Join Date
    Apr 2001
    Location
    uk
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I know now that my database design wasn't too good when I made my articles script but... I can't (be bothered?) to change it now, so anyway. I have a table it has fields a bit like this.

    Writer | Title | Content | Date Written

    there are more but they are irrelivent. Basically, what I want to do, is grab the name (writer) that appears the most often in the table, the most prolific writer, if you will.

    I have been told by several sources that this can be done with the GROUP comand, but I'm still not sure how, if somebody could tell me how it's done, and possibly, if they have time, give me a brief explanation of how GROUP (BY) works, I'd be most grateful indeed

  2. #2
    SitePoint Wizard
    Join Date
    Mar 2001
    Posts
    3,537
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    I am not real familiar with GROUP BY, but I know basically what it is. If you had this query:

    SELECT max(salary)
    FROM employee;

    you would get the maximum salary from the salary column of your employee table. If you alter the statement like this:

    SELECT max(salary), department
    FROM employee
    GROUP BY department;

    then you would get the maximum salary for each distinct department value in your table, i.e. several values rather than one value, AND the department name would be listed for every max(salary).

    Here is an interactive tutorial on GROUP BY. Look at the Aggregate Functions first. If you do the brief exercises you will get a basic understanding.

    http://www.sqlcourse2.com/groupby.html

    For your purposes, I think you could do something like this:

    SELECT COUNT(Title), Writer
    FROM mytable
    GROUP BY Writer;

    That would return rows consisting of the total number of articles by an author followed by the author's name. I think it will list the authors alphabetically A-Z.
    Last edited by 7stud; Apr 29, 2001 at 07:14.

  3. #3
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    SELECT COUNT(*) AS works, Writer
    FROM tableName
    GROUP BY Writer
    ORDER BY works DESC
    LIMIT 1

    That should return a result set with one row containing the number of works ("works") and the name ("Writer") of the most prolific writer.

  4. #4
    SitePoint Zealot DarkMonkey's Avatar
    Join Date
    Apr 2001
    Location
    uk
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks people, your the best


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •