Sequence of rows

[viki1967]

Hello everyone;

In the table MYSQL I have this sequence of rows:

CODE=========== NAME
610============ =ASS
610============ =ASS
610============ =ASS
610============ =ASS
619============ =ASS-9
619============ =ASS-9
619============ =ASS-9

I like to see:

ASS

and query that counts for all rows like ASS...

Can you help me ?
Regards

[deric]

Is the value ASS fix?
Try any of these...

select distinct name, count(name) from table where name = 'ASS' 
/*will result to:
ASS, 4 */

select distinct name, count(name) from table 
/*will result to:
ASS, 4
ASS-9, 3*/

select distinct name count(name) from table where name like 'ASS%'
/*will result to:
ASS, 4
ASS-9, 3*/

[viki1967]

Many thanks Deric for your reply.

First I write you one example.

The real situation in the table mysql is:

9168383 510 >>>>> DTR SAR-ZO OR-UOS ORISTANO
9168383 519 >>>>> DTR SAR-ZO OR-UOS ORISTANO-9
9168383 110 >>>>> DTR SAR-ZO OR-PRG-PROGRAM.OPERATI VA
9168383 119 >>>>> DTR SAR-ZO OR-PRG-PROGRAM.OPERATI VA - 1
9168383 140 >>>>> DTR SAR-ZO OR-PRG-VERIFICHE
9168383 120 >>>>> DTR SAR-ZO OR-PRG-SICUREZZA E AMBIENTE
9168383 130 >>>>> DTR SAR-ZO OR-PRG-GESTIONE CONNESSIONI
9168383 100 >>>>> DTR SAR-ZO OR-PROGRAMMAZIONE E GESTIONE

This rows are divided by date ( 2008-04-01, 2008-04-02, ... ) and name of the person responsable ( Jim for 9168383 510, John for 9168383 519, ... ) of the unity.

I like to see:

9168383 510 >>>>> DTR SAR-ZO OR-UOS ORISTANO
9168383 110 >>>>> DTR SAR-ZO OR-PRG-PROGRAM.OPERATI VA
9168383 140 >>>>> DTR SAR-ZO OR-PRG-VERIFICHE
9168383 120 >>>>> DTR SAR-ZO OR-PRG-SICUREZZA E AMBIENTE
9168383 130 >>>>> DTR SAR-ZO OR-PRG-GESTIONE CONNESSIONI
9168383 100 >>>>> DTR SAR-ZO OR-PROGRAMMAZIONE E GESTIONE

And query that counts for all rows like DTR SAR-ZO OR and counts for all rows the same to:

9168383 510 >>>>> DTR SAR-ZO OR-UOS ORISTANO
9168383 110 >>>>> DTR SAR-ZO OR-PRG-PROGRAM.OPERATI VA
9168383 140 >>>>> DTR SAR-ZO OR-PRG-VERIFICHE
9168383 120 >>>>> DTR SAR-ZO OR-PRG-SICUREZZA E AMBIENTE
9168383 130 >>>>> DTR SAR-ZO OR-PRG-GESTIONE CONNESSIONI
9168383 100 >>>>> DTR SAR-ZO OR-PROGRAMMAZIONE E GESTIONE

You understand me ?

If you don't understand all this I can send you the original table mysql.

Thans for your attention.
Viki

[deric]

Yeah, sorry, it is hard to understand.
You may post the table schema here.

How did you arrive with the rows that you want to see? I mean, what are the conditions/requirements to get them? Through date, person responsible, or what?

Please supply more input... don't hesitate to post a long message, if that will clearly explain what you're trying to say.



***************
To any moderator:
This is not an ASP problem, but a mySQL's. Please move this so that it will be better helped. Thanks.

[viki1967]

OK Deric, thanks for your help.

This is the input table MySQL:

http://www.complessobandisticopalombarasabina.it/viki1967/tbl.zip

Output:

http://www.complessobandisticopalombarasabina.it/viki1967/immagine.jpg

Please help me...
Viki

[deric]

So "DTR SAR-ZO OR" is fixed? No other values that will start differently?
My problem is, where did you get the code '9168383500"? In you table, there is no row with that code... Anyway, I think you can work it out for yourself now.

Try this simple approach:

SELECT '9168383500' AS CODE, 'DTR SAR-ZO OR' AS NAME, COUNT(*) AS NUMBER FROM tbl WHERE Name LIKE 'DTR SAR-ZO OR%'

UNION

SELECT DISTINCT Code, Name, COUNT(*) FROM tbl WHERE NAME LIKE 'DTR SAR-ZO OR%' GROUP BY Name

The above code will first get the count of all the rows with DTR SAR-ZO OR on their name, then combine it with the count of all the rows grouped according to Name.

[viki1967]

thanks your query working !!!

[deric]

That's good to know. Cheers!

[DrBunchman]

To any moderator:
This is not an ASP problem, but a mySQL's. Please move this so that it will be better helped. Thanks.

Done (sorry only just noticed this post!).