Results 1 to 15 of 15
  1. #1
    Join Date
    Oct 2007
    Posts
    3

    Question Unanswered: Counter specific to a field

    Hello All,

    Happy Halloween to everybody

    I took this example from webdev notes that help me explain what I am trying to do perfectly.


    HTML Code:
    select title, count(*)
    from employee_data GROUP BY title;
    
    +----------------------------+----------+
    | title                      | count(*) |
    +----------------------------+----------+
    | CEO                        |        1 |
    | Customer Service Manager   |        1 |
    | Finance Manager            |        1 |
    | Marketing Executive        |        3 |
    | Multimedia Programmer      |        3 |
    | Programmer                 |        4 |
    | Senior Marketing Executive |        1 |
    | Senior Programmer          |        2 |
    | Senior Web Designer        |        1 |
    | System Administrator       |        2 |
    | Web Designer               |        2 |
    +----------------------------+----------+
    I would like to have the following result instead:

    HTML Code:
    +----------------------------+----------+
    | title                      | Count |
    +----------------------------+----------+
    | CEO                        |        1 |
    | Customer Service Manager   |        1 |
    | Finance Manager            |        1 |
    | Marketing Executive        |        1 |
    | Marketing Executive        |        2 |
    | Marketing Executive        |        3 |
    | Multimedia Programmer      |        1 |
    | Multimedia Programmer      |        2 |
    | Multimedia Programmer      |        3 |
    | Programmer                 |        1 |
    | Programmer                 |        2 |
    | Programmer                 |        3 |
    | Programmer                 |        4 |
    | Senior Marketing Executive |        1 |
    | Senior Programmer          |        1 |
    | Senior Programmer          |        2 |
    | Senior Web Designer        |        1 |
    | System Administrator       |        1 |
    | System Administrator       |        2 |
    | Web Designer               |        1 |
    | Web Designer               |        2 |
    +----------------------------+----------+
    I have no clue how to do that!

    Is someone would have a brilliant idea?

    Thank you very much in advance

  2. #2
    Join Date
    May 2005
    Location
    San Antonio, Texas
    Posts
    134
    :P The closest I can get to that is:

    Code:
    select a.a, count from test a inner join (select a, count(*) as count from test group by a) as b on (a.a = b.a);
      a   | count
    ------+-------
     Hi   |     3
     Hi   |     3
     Hi   |     3
     Joe  |     2
     Joe  |     2
     Joey |     5
     Joey |     5
     Joey |     5
     Joey |     5
     Joey |     5
     Max  |     1
    (11 rows)
    I am not sure what you want is possible. That is an incremental count of items as they are found. count aggregates all the objects together as restricted by group by and counts them As far as I know, there is no incremental count that would process the results as it goes through it. Maybe someone is aware of a mysql function that would do that.

    May I ask as to why you need to do this? It seems to be an odd thing
    Vi veri veniversum vivus vici
    By the power of truth, I, a living man, have conquered the universe

  3. #3
    Join Date
    Oct 2007
    Posts
    3
    Hey amthomas,

    Thank you for trying.

    Someone in my team is running a report that is based on this field, right now the column is made by hand in excel and it takes for ages.
    I will redesign the report in the next few weeks but I wanted to at least fix this quick to speed up this process.

    thank you

    Tech

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Try something like this
    Code:
    create table emp (
            emp_title       varchar(40),
            emp_name        varchar(40)
    );
    
    insert emp (emp_title, emp_name) values ( "CEO","Henry" );
    insert emp values ( 'Senior Marketing Executive','Walter' );
    insert emp values ( 'Senior Marketing Executive','Freud' );
    insert emp values ( 'Senior Marketing Executive','Wally' );
    insert emp values ( 'Senior Web Designer','Pete' );
    insert emp values ( 'Senior Web Designer','Burt' );
    
    -- this is the important bit
    select emp_title,
            ( select count(*)
            from emp e2
            where e2.emp_title = e1.emp_title
                    and e2.emp_name <= e1.emp_name ) as id
    from emp e1
    order by emp_title, id
    
    drop table emp;
    Mike

  5. #5
    Join Date
    Oct 2007
    Posts
    3

    Thank you

    Thank you Mike!

    I works great! It is very helpful.

    Tech

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    run this --
    Code:
    insert emp values ( 'Senior Marketing Executive','Wally' );
    now try mike's query again

    Senior Marketing Executive 1
    Senior Marketing Executive 3
    Senior Marketing Executive 3
    Senior Marketing Executive 4

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Originally posted by technofixer
    Thank you Mike!
    my pleasure

    EDIT I have removed my response to r937 as I misunderstood his point.

    Mike
    Last edited by mike_bike_kite; 11-02-07 at 04:49.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    there you go again, mike, insulting fellow board members
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    EDIT removed due to misunderstanding
    Mike
    Last edited by mike_bike_kite; 11-02-07 at 04:48.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i was merely pointing out the flaw in your SQL

    there was nothing personal about it

    certainly no "little fun remarks"

    you need to relax, chill, stop overreacting, and remain civil towards other people, especially moderators who could easily one day get totally fed up with your puerile behaviour and ban you
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    EDIT removed due to misunderstanding
    Mike
    Last edited by mike_bike_kite; 11-02-07 at 04:47.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    mike, i believe i apologized for that remark, did i not?

    it was wrong, and i apologize again

    will you please let it go?

    and yes, there was a flaw in the SQL -- but not, i hasten to say, in you -- if two people have the same name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Originally posted by r937
    and yes, there was a flaw in the SQL -- but not, i hasten to say, in you -- if two people have the same name
    Well spotted! Hopefully technofixer can use a unique field as a substitute. I did get the wrong end of the stick in your initial post - I thought you were taking a swipe about not having a PK on the test table. Please feel free to delete my posts since your initial email.

    Sorry.

    Mike

  14. #14
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    It's just us english folk rudy, we're so darned aggressive... explains the size of our former empire, if you don't agree we'll conquer and enslave you and force our religous views upon you, hehe!

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by aschk
    if you don't agree we'll conquer and enslave you and force our religous views upon you, hehe!
    That concept works ok for a while, given sufficient technology and determination. As the UK (and recently the US) found out, it doesn't make for a good long-term plan!

    Different parts of the world work in different ways. That's a good thing in my book, I love the variety. You can stand on the banks of the Nile, the Amazon, the Mississippi, the Thames, and the Ganges and marvel at how similar and how different things are, and I love that!

    -PatP

Posting Permissions

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