sql statement mysql notcorrect

SELECT SUBSTRING(m.own,3,4) as c , (select  amphur.AMPHUR_NAME where c = SUBSTRING(m.own,3,4)  ),
         COUNT(* ) AS cnt 
    FROM MEMBER AS m 

   GROUP BY SUBSTRING(m.own,3,4)
order by cnt开发者_高级运维 desc

sql statement mysql what wrong with code below when i fill

(select  amphur.AMPHUR_NAME where c = SUBSTRING(m.own,3,4)  )

it error

CREATE TABLE IF NOT EXISTS `member` (
  `idmember` int(11) NOT NULL AUTO_INCREMENT,
  `own` varchar(255) DEFAULT NULL,
  `Sname` varchar(255) DEFAULT NULL,
  `Ssurname` varchar(255) DEFAULT NULL,
  `Sex` enum('¿','¿') NOT NULL,
  `Hno` varchar(255) DEFAULT NULL,
  `Moo` varchar(255) DEFAULT NULL,
  `tambol` varchar(200) NOT NULL,
  `dateofbirth` date DEFAULT NULL,
  `migratedate` date DEFAULT NULL,
  `status` enum('5','4','3','2','1') DEFAULT '5',
  `Unit` int(4) DEFAULT NULL,
  `staff1` int(11) DEFAULT NULL,
  `staff2` int(11) DEFAULT NULL,
  `fathercode` varchar(30) NOT NULL,
  `mathercode` varchar(30) NOT NULL,
  PRIMARY KEY (`idmember`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=8994 ;


CREATE TABLE IF NOT EXISTS `amphur` (
  `AMPHUR_ID` int(5) NOT NULL AUTO_INCREMENT,
  `AMPHUR_CODE` varchar(4) COLLATE utf8_unicode_ci NOT NULL,
  `AMPHUR_NAME` varchar(150) COLLATE utf8_unicode_ci NOT NULL,
  `GEO_ID` int(5) NOT NULL DEFAULT '0',
  `PROVINCE_ID` int(5) NOT NULL DEFAULT '0',
  `province_name` varchar(80) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`AMPHUR_ID`),
  KEY `province_name` (`province_name`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=999 ;

Your subquery is missing a From clause:

SELECT SUBSTRING(m.own,3,4) as c 
    , (select amphur.AMPHUR_NAME 
        From amphur
        Where ??? = SUBSTRING(m.own,3,4) )
    , COUNT(* ) AS cnt 
FROM MEMBER AS m 

However, how does the amphur table relate to the member table?

You cannot use aliases in the same level.

Even if you could, you are filtering on non-correlated columns in your subquery: the subquery would just return the record from amphur if there is one record, or an error if there are more.

Could you please provide some sample data and the desired recordset?

there is no "FROM" clause in your select Amphur.amphur_name