使用查询从mysql表中获取字段的总和

i have the following table in my databse..

我的数据库中有下表

CREATE TABLE IF NOT EXISTS `client` (
  `CARD_NO` varchar(15) NOT NULL,
  `F_NAME` varchar(20) NOT NULL,
  `L_NAME` varchar(20) NOT NULL,
  `SEX` varchar(5) NOT NULL,
  `DOB` date NOT NULL,
  `SUBCITY` varchar(10) NOT NULL,
  `KEBELE` varchar(5) NOT NULL,
  `HOUSE_NO` varchar(10) NOT NULL,
  `TEL_NO` int(10) DEFAULT NULL,
  `REGION_NO` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`CARD_NO`),
  KEY `REGION_NO` (`REGION_NO`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `client`
--

INSERT INTO `client` (`CARD_NO`, `F_NAME`, `L_NAME`, `SEX`, `DOB`, `SUBCITY`, `KEBELE`, `HOUSE_NO`, `TEL_NO`, `REGION_NO`) VALUES
('1', '', '', 'male', '0000-00-00', '', '', '', NULL, '01'),
('2', '', '', 'male', '0000-00-00', '', '', '', NULL, '02'),
('3', '', '', 'femal', '0000-00-00', '', '', '', NULL, '03'),
('4', '', '', 'femal', '0000-00-00', '', '', '', NULL, '04'),
('5', '', '', 'male', '0000-00-00', '', '', '', NULL, '05'),
('6', '', '', 'male', '0000-00-00', '', '', '', NULL, '05');

-- --------------------------------------------------------

--
-- Table structure for table `non_pregnant`
--

CREATE TABLE IF NOT EXISTS `non_pregnant` (
  `VCT_CODE` varchar(15) NOT NULL,
  `CARD_NO` varchar(15) DEFAULT NULL,
  `RISK_OF_HIV` varchar(30) NOT NULL,
  `PURPOSE` varchar(10) NOT NULL,
  PRIMARY KEY (`VCT_CODE`),
  KEY `CARD_NO` (`CARD_NO`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `non_pregnant`
--

INSERT INTO `non_pregnant` (`VCT_CODE`, `CARD_NO`, `RISK_OF_HIV`, `PURPOSE`) VALUES
('1', '1', '', ''),
('2', '2', '', ''),
('3', '3', '', ''),
('4', '4', '', ''),
('5', '5', '', ''),
('6', '6', '', '');

-- --------------------------------------------------------

--
-- Table structure for table `non_pregnant_vct`
--

CREATE TABLE IF NOT EXISTS `non_pregnant_vct` (
  `VCT_CODE` varchar(15) NOT NULL DEFAULT '',
  `DATE_TESTED` date DEFAULT NULL,
  `TB_RESULT` varchar(10) NOT NULL,
  `HIV_RESULT` varchar(10) NOT NULL,
  `STD_RESULT` varchar(10) NOT NULL,
  PRIMARY KEY (`VCT_CODE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `non_pregnant_vct`
--

INSERT INTO `non_pregnant_vct` (`VCT_CODE`, `DATE_TESTED`, `TB_RESULT`, `HIV_RESULT`, `STD_RESULT`) VALUES
('1', NULL, '', 'pos', ''),
('2', NULL, '', 'pos', ''),
('3', NULL, '', 'neg', ''),
('4', NULL, '', 'neg', ''),
('5', NULL, '', 'neg', ''),
('6', NULL, '', 'pos', '');

-- --------------------------------------------------------

--
-- Table structure for table `region`
--

CREATE TABLE IF NOT EXISTS `region` (
  `REGION_NO` varchar(10) NOT NULL,
  `REGION_NAME` varchar(10) NOT NULL,
  PRIMARY KEY (`REGION_NO`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `region`
--

INSERT INTO `region` (`REGION_NO`, `REGION_NAME`) VALUES
('01', 'washington'),
('02', 'newyork'),
('03', 'london'),
('04', 'paris'),
('05', 'burlin');

here is what i want as output..

这是我想要的输出..

Region Female Male Total Test Positive Test Positive Test Positive              

and this is the query i tried to figure it out but still wrong..

这是我试图弄清楚但仍然错误的查询..

SELECT r.region_name, c.sex = 'male' as counting, c.sex = 'femal' as counting, v.hiv_result = 'pos' as MalePos from region r
left join client c
on c.region_no = r.region_no
left join non_pregnant p
on c.card_no = p.card_no
left join non_pregnant_vct v
on p.vct_code = v.vct_code
group by c.region_no;

1 个解决方案

#1

Your description of the output you want is breathtakingly unclear, but I'll give it a shot. Try this:

你想要的输出描述令人惊讶地不清楚,但我会试一试。试试这个:

SELECT
    r.region_name,
    SUM(c.sex = 'male') as NMale,
    SUM(c.sex = 'femal') as NFemale,
    SUM(v.hiv_result = 'pos') as TotalPos,
    SUM(c.sex = 'male' AND v.hiv_result = 'pos') as MalePos,
    SUM(c.sex = 'femal' AND v.hiv_result = 'pos') as FemalePos
FROM region r
LEFT JOIN client c ON c.region_no = r.region_no
LEFT JOIN non_pregnant p ON c.card_no = p.card_no
LEFT JOIN non_pregnant_vct v ON p.vct_code = v.vct_code
GROUP BY c.region_no