Is it possible to have multiple HAVING in one query?
Here is my sample query:
SELECT household_tbl.household_connector_id AS h_id
, (
SELECT COUNT(household_connector_id)
FROM household_tbl
WHERE household_connector_id = h_id
AND hh_phic_status = 1
AND age >= 21
AND age_category = "Year"
) + (
SELECT COUNT(hh_members_connector_id)
FROM tbl_household_members
WHERE household_connector_id = h_id
AND hh_phic_status = 1
AND hh_age >= 21
AND hh_age_category = "Year"
) AS total_phic
, (
SELECT COUNT(household_connector_id)
FROM household_tbl
WHERE household_connector_id = h_id
AND hh_phic_status = 1
AND age >= 21
AND age_category = "Year"
AND (
hh_phic_membership_category = "ng sponsored"
OR hh_phic_membership_category = "Sponsored-NG(DOH)"
)
) + (
SELECT COUNT(hh_members_connector_id)
FROM tbl_household_members
WHERE household_connector_id = h_id
AND hh_phic_status = 1
AND hh_age >= 21
AND hh_age_category = "Year"
AND (
hh_phic_membership_category = "ng sponsored"
OR hh_phic_membership_category = "Sponsored-NG(DOH)"
)
) AS ng_sponsored
FROM household_tbl
WHERE barangay = "'.$barangay.'"
HAVING total_phic <> 0
I want to have also "HAVING ng_sponsored <> 0"
Thanks!
Do this:
Select * From
(
SELECT household_tbl.household_connector_id as h_id,
(SELECT COUNT(household_connector_id)
FROM household_tbl
WHERE household_connector_id = h_id
AND hh_phic_status = 1
AND age >= 21
AND age_category = "Year") +
(SELECT COUNT(hh_members_connector_id)
FROM tbl_household_members
WHERE
household_connector_id = h_id
AND hh_phic_status = 1
AND hh_age >= 21
AND hh_age_category = "Year")
as total_phic,
(SELECT COUNT(household_connector_id)
FROM household_tbl
WHERE household_connector_id = h_id
AND hh_phic_status = 1
AND age >= 21
AND age_category = "Year"
AND (hh_phic_membership_category = "ng sponsored" OR hh_phic_membership_category = "Sponsored-NG(DOH)")) +
(SELECT COUNT(hh_members_connector_id)
FROM tbl_household_members
WHERE household_connector_id = h_id
AND hh_phic_status = 1
AND hh_age >= 21
AND hh_age_category = "Year"
AND (hh_phic_membership_category = "ng sponsored" OR hh_phic_membership_category = "Sponsored-NG(DOH)"))
as ng_sponsored
FROM household_tbl WHERE barangay = "'.$barangay.'"
)tbl Group By h_id,total_phic,ng_sponsored
HAVING total_phic <> 0
You can replace:
Group By h_id,total_phic,ng_sponsored HAVING total_phic <> 0
By
Where total_phic <> 0
You can calculate each count which satisfies the condition in a subquery. The filtering operator that is needed here is only WHERE and not HAVING because you have no further aggregation on the outer query.
SELECT a.household_connector_id AS h_id,
COALESCE(b.totalCount, 0) + COALESCE(c.totalCount, 0) AS total_phic,
COALESCE(b.totalCountSponsored, 0) + COALESCE(c.totalCountSponsored, 0) AS ng_sponsored
FROM household_tbl a
INNER JOIN
(
SELECT household_connector_id,
COUNT(*) totalCount,
SUM(hh_phic_membership_category IN ('ng sponsored', 'Sponsored-NG(DOH)')) totalCountSponsored
FROM household_tbl
WHERE hh_phic_status = 1 AND
age >= 21 AND
age_category = "Year"
GROUP BY household_connector_id
) b ON a.household_connector_id = b.household_connector_id
LEFT JOIN
(
SELECT household_connector_id,
COUNT(*) totalCount,
SUM(hh_phic_membership_category IN ('ng sponsored', 'Sponsored-NG(DOH)')) totalCountSponsored
FROM tbl_household_members
WHERE hh_phic_status = 1 AND
hh_age >= 21 AND
hh_age_category = "Year"
GROUP BY household_connector_id
) c ON a.household_connector_id = c.household_connector_id
WHERE a.barangay = "barangay_value_HERE" AND
COALESCE(b.totalCount, 0) + COALESCE(c.totalCount, 0) <> 0 AND
COALESCE(b.totalCountSponsored, 0) + COALESCE(c.totalCountSponsored, 0) <> 0
You cannot also use an ALIAS on the WHERE clause which is created on the same level because the ALIAS were created on SELECT clause and WHERE clause is executed before SELECT. Here's the complete SQL Order of Operation,