Query SQL GROUP BY

[viki1967]

Query SQL GROUP BY

Hello everyone.

I have this problem with table of database MySQL.

My table with fields ID, Number and Description:

[b]ID ====== NUMBER ======= DESCRIPTION[/b]
1  ====== 9168381650==== CDR VER-CS AP-UPS SANLUCA
2  ====== 9168381659==== CDR VER-CS AP-UPS SANLUCA-9
3  ====== 9168381651==== CDR VER-CS AP-UPS SANLUCA-SQ S.SANLUCA
4  ====== 9168381652==== CDR VER-CS AP-UPS SANLUCA-SQ DIST.GUSPIN

From this table I need to have the total of records data all under the description CDR VER AP-CS-UPS SANLUCA.

Is a hierarchy structure where IDs 2, 3 and 4 belong to the description ID 1.

I don't like to see the data individually for each description, but the grouping data for macro-DESCRIPTION.

For example:

The total records in table are 4, the query return:
"There are 4 records total for description CDR VER AP-CS-UPS SANLUCA."

thanks for your help, regards.
viki

[cryogeneric]

Query SQL GROUP BY

Hello everyone.

I have this problem with table of database MySQL.

My table with fields ID, Number and Description:

[b]ID ====== NUMBER ======= DESCRIPTION[/b]
1  ====== 9168381650==== CDR VER-CS AP-UPS SANLUCA
2  ====== 9168381659==== CDR VER-CS AP-UPS SANLUCA-9
3  ====== 9168381651==== CDR VER-CS AP-UPS SANLUCA-SQ S.SANLUCA
4  ====== 9168381652==== CDR VER-CS AP-UPS SANLUCA-SQ DIST.GUSPIN

From this table I need to have the total of records data all under the description CDR VER AP-CS-UPS SANLUCA.

Is a hierarchy structure where IDs 2, 3 and 4 belong to the description ID 1.

I don't like to see the data individually for each description, but the grouping data for macro-DESCRIPTION.

For example:

The total records in table are 4, the query return:
"There are 4 records total for description CDR VER AP-CS-UPS SANLUCA."

thanks for your help, regards.
viki

Well, if you're specifically trying to bring back the row count for records containing the description "CDR VER AP-CS-UPS SANLUCA" ?

That would be fairly simple, just:

select count(*) 
from table 
where DESCRIPTION like "CDR VER AP-CS-UPS SANLUCA%"

Since the descriptions are different on every row, you won't be able to bring back a single row if you are attempting to include the description in the query with a normal group by.

In other words, this won't work:

select count(*), DESCRIPTION
from table 
where DESCRIPTION like "CDR VER AP-CS-UPS SANLUCA%"
group by DESCRIPTION

This will return a count of "1" for EACH possible description.

Now, if you are SPECIFICALLY looking for rows with a description like "CDR VER AP-CS-UPS SANLUCA" there is one solution. However, this ONLY works if you are NOT trying to do something dynamic. You can't run this against an entire table of different descriptions and expect accurate results. It only works for the description you mentioned.

You can substring your description and group by that...

select count(*), Substring(DESCRIPTION,1,25)
from table
where DESCRIPTION like 'CDR VER AP-CS-UPS SANLUCA%'
group by Substring(DESCRIPTION,1,25)

The will return a single row with count of 4 and the description "'CDR VER AP-CS-UPS SANLUCA"

[viki1967]

Thanks for your reply.

The query is good if in the table I have only DESCRIPTION = CDR VER AP-CS-UPS SANLUCA.

The table contains data of this type:

ID ====== NUMBER ====== DESCRIPTION
1 ====== 9168381650==== CDR VER-CS AP-UPS SANLUCA
2 ====== 9168381659==== CDR VER-CS AP-UPS SANLUCA-9
3 ====== 9168381651==== CDR VER-CS AP-UPS SANLUCA-SQ S.SANLUCA
4 ====== 9168381652==== CDR VER-CS AP-UPS SANLUCA-SQ DIST.GUSPIN

5 ====== 9168381653==== CDR VER-CS AP-UPS SANVITO
6 ====== 9168381656==== CDR VER-CS AP-UPS SANVITO-9
7 ====== 9168381658==== CDR VER-CS AP-UPS SANVITO-SQ S.SANVITO
8 ====== 9168381655==== CDR VER-CS AP-UPS SANVITO-SQ DIST.GUSPIN

5 ====== 9168381643==== CDR VER-CS AP-UPS SANPIO
6 ====== 9168381646==== CDR VER-CS AP-UPS SANPIO-9
7 ====== 9168381648==== CDR VER-CS AP-UPS SANPIO-SQ S.SANPIO
8 ====== 9168381645==== CDR VER-CS AP-UPS SANPIO-SQ DIST.GUSPIN

Are 5000 records groups by 4 where and DESCRIPTION and NUMBER are always different ...

Your query is valid only for CDR VER AP-CS-UPS SANLUCA...

The problem is that:

Counter rows in the table for description and see only macro-description...

CDR VER-CS AP-UPS SANLUCA ===> 4 rows
CDR VER-CS AP-UPS SANVITO ===> 4 rows
CDR VER-CS AP-UPS SANPIO ===> 4 rows

[cryogeneric]

There has to be something consistent about the descriptions or hierarchy structures to do what you need to do.

There isn't a way for SQL to know what "root" description you're attempting to group by. Without something unique to group these structures with, you're going to be limited in what results you can return.

Is there something unique within each structure? Or is there something consistent with the descriptions that we can use to identify a "root" description?

[viki1967]

Yes, I resolved.
thanks