mysql query

Question

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;

Answer 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