[technofixer]

Hello All,

Happy Halloween to everybody

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

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:

+----------------------------+----------+
| 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

[amthomas]

:P The closest I can get to that is:

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

[technofixer]

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

[mike_bike_kite]

Try something like this

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

[technofixer]

Thank you Mike!

I works great! It is very helpful.

Tech

[r937]

run this --

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

[mike_bike_kite]

Originally posted by technofixer
Thank you Mike!

my pleasure

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

Mike

[r937]

there you go again, mike, insulting fellow board members

[mike_bike_kite]

EDIT removed due to misunderstanding
Mike

[r937]

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

[mike_bike_kite]

EDIT removed due to misunderstanding
Mike

[r937]

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

[mike_bike_kite]

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

[aschk]

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!

[Pat Phelan]

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