Query SQL GROUP BY

[viki1967]

Hello everyone.

I have this problem with table of database MySQL.

My table with fields ID, Number and Description:


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


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

[mjlorbet]

select count(*) as num_records from your_table where description like '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...

[mjlorbet]

so you're looking for pattern matching on returned descriptions along with counts on each, an interesting idea but i'm not sure sql is equipped for such a thing. you can however extend the processing to another language, return your groups & do more manipulation with php or whatever interface you'll be using to display/utilize the data (also note, that i did realize that you would have more than data that matched that particular pattern and the code i posted was an example, having a database with records that all started with the exact same thing would just be stupid as the prefix could be assumed to save space)

[viki1967]

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

[mjlorbet]

exactly, but how are you going to get mysql to determine the unique base names? by number of letters, or what? you need to have some sort of matching so that you can perform a select on them to count the number of records returned. you'll definately need a stored procedure to accomplish this, you'll also need a table of the base names to iterate through to find elements that match them, otherwise you're stuck trying to do inline string manipulation in a sql select statement, which i don't think would work out so well


CREATE TABLE #tmp (desc as varchar(50))
GO
SELECT Description INTO #tmp FROM yourTable
GO
CREATE TABLE #ncp (nname as varchar(50), num as int)
GO
SELECT SELECT TOP 1 * FROM #tmp as nname, count(*) as num INTO #ncp FROM yourTable where Description like (nname + '%')
GO
DELETE FROM #tmp WHERE desc = SELECT TOP 1 * FROM #tmp as nname
GO


now my sql's a little rusty & i don't have my quick reference available, but you'll need to find some manner of flow control to loop through the last 2 (4 if you count GOs) lines until all the records are removed from #tmp, afterwords the base names and counts should be stored in #ncp

[viki1967]

Sorry, i do not understand your data...

My table mysql:



# MySQL-Front 3.2 (Build 13.39)

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='SYSTEM' */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE */;
/*!40101 SET SQL_MODE='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES */;
/*!40103 SET SQL_NOTES='ON' */;


# Host: localhost Database: test
# ------------------------------------------------------
# Server version 5.0.21-community-nt

#
# Table structure for table my_tbl
#

DROP TABLE IF EXISTS `my_tbl`;
CREATE TABLE `my_tbl` (
`ID` int(10) NOT NULL auto_increment,
`Number` varchar(100) default '',
`Description` varchar(255) default NULL,
`Date` date default NULL,
PRIMARY KEY (`ID`),
KEY `Number` (`Number`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

#
# Dumping data for table my_tbl
#

INSERT INTO `my_tbl` VALUES (1,'9168381650','CDR VER-CS AP-UPS SANLUCA','2008-03-11');
INSERT INTO `my_tbl` VALUES (2,'9168381651','CDR VER-CS AP-UPS SANLUCA-9','2008-03-11');
INSERT INTO `my_tbl` VALUES (3,'9168381652','CDR VER-CS AP-UPS SANLUCA-SQ S.SANLUCA','2008-03-12');
INSERT INTO `my_tbl` VALUES (4,'9168381653','CDR VER-CS AP-UPS SANLUCA-SQ DIST.GUSPIN','2008-03-13');
INSERT INTO `my_tbl` VALUES (5,'9168381654','CDR VER-CS AP-UPS SANVITO','2008-03-14');
INSERT INTO `my_tbl` VALUES (6,'9168381655','CDR VER-CS AP-UPS SANVITO-9','2008-03-15');
INSERT INTO `my_tbl` VALUES (7,'9168381656','CDR VER-CS AP-UPS SANVITO-SQ S.SANVITO','2008-03-16');
INSERT INTO `my_tbl` VALUES (8,'9168381657','CDR VER-CS AP-UPS SANVITO-SQ DIST.GUSPIN','2008-03-17');
INSERT INTO `my_tbl` VALUES (9,'9168381658','CDR VER-CS AP-UPS SANPIO','2008-03-18');
INSERT INTO `my_tbl` VALUES (10,'9168381659','CDR VER-CS AP-UPS SANPIO-9','2008-03-19');
INSERT INTO `my_tbl` VALUES (11,'9168381660','CDR VER-CS AP-UPS SANPIO-SQ S.SANPIO','2008-03-20');
INSERT INTO `my_tbl` VALUES (12,'9168381661','CDR VER-CS AP-UPS SANPIO-SQ DIST.GUSPIN','2008-03-21');
INSERT INTO `my_tbl` VALUES (13,'9168381650','CDR VER-CS AP-UPS SANLUCA','2008-03-22');
INSERT INTO `my_tbl` VALUES (14,'9168381651','CDR VER-CS AP-UPS SANLUCA-9','2008-03-23');
INSERT INTO `my_tbl` VALUES (15,'9168381652','CDR VER-CS AP-UPS SANLUCA-SQ S.SANLUCA','2008-03-24');
INSERT INTO `my_tbl` VALUES (16,'9168381653','CDR VER-CS AP-UPS SANLUCA-SQ DIST.GUSPIN','2008-03-25');
INSERT INTO `my_tbl` VALUES (17,'9168381654','CDR VER-CS AP-UPS SANVITO','2008-03-26');
INSERT INTO `my_tbl` VALUES (18,'9168381655','CDR VER-CS AP-UPS SANVITO-9','2008-03-27');
INSERT INTO `my_tbl` VALUES (19,'9168381656','CDR VER-CS AP-UPS SANVITO-SQ S.SANVITO','2008-03-28');
INSERT INTO `my_tbl` VALUES (20,'9168381657','CDR VER-CS AP-UPS SANVITO-SQ DIST.GUSPIN','2008-03-29');

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

[mjlorbet]

create another single column table that contains only the bases of the descriptions, not the variants on the name, then you cycle though each of the base names counting the number of records with descriptions like it

[viki1967]

This is the solution:




sql = " SELECT CONCAT(left_part, next_part) AS macro "
sql = sql & " , COUNT(*) AS numrows "
sql = sql & " FROM ( SELECT id "
sql = sql & " , number "
sql = sql & " , LEFT(description,18) AS left_part "
sql = sql & " , SUBSTRING_INDEX( "
sql = sql & " SUBSTR(description FROM 19) "
sql = sql & ", '-',1) AS next_part "
sql = sql & " FROM my_tbl ) AS d "
sql = sql & " GROUP "
sql = sql & " BY macro "

Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open sql, objconn

response.write sql & "<br>"

if not objRS.eof then

objRS.MoveFirst()
Do While Not objRS.EOF

response.write objRs("macro") & "--->" & objRs("numrows") & "<br>"

objRS.MoveNext()
Loop

end if

objRS.Close
Set objRS = Nothing

objconn.Close
Set objconn = Nothing



thanks
viki