i need to make a single query with the following. pls give me a tip and advise me on this. tq
// Cash meal PUPIL income calc NOT INC NURSERY
SELECT concat( ‘£’, format( sum( price ) , 2 ) ) AS CPM, count( mid ) AS NOS
FROM meals, parents, pupils
WHERE meals.school_id =46
AND (
meals.date
BETWEEN “20110102”
AND “20110108”
)
AND meals.parent_id = parents.id
AND typeCode = ‘1’
AND meals.pupil_id = pupils.id
AND pupils.year != ‘9’
// Cash meal PUPIL income calc NURSERY ONLY
SELECT concat( ‘£’, format( sum( price ) , 2 ) ) AS CNM, count( mid ) AS NOS
FROM meals, parents, pupils
WHERE meals.school_id =46
AND (
meals.date
BETWEEN “20110102”
AND “20110108”
)
AND meals.parent_id = parents.id
AND typeCode = ‘1’
AND meals.pupil_id = pupils.id
AND pupils.year = ‘9’
// Cash meal ADULT income calc
SELECT concat( ‘£’, format( sum( price ) , 2 ) ) AS CNM, count( mid ) AS NOS
FROM meals, parents, pupils
WHERE meals.school_id =46
AND (
meals.date
BETWEEN “20110102”
AND “20110108”
)
AND meals.parent_id = parents.id
AND typeCode = ‘1’
AND meals.pupil_id = pupils.id
AND typeCode = ‘3’
// Free meal PUPIL income calc NOT NURSERY
SELECT concat( ‘£’, format( sum( price ) , 2 ) ) AS CNM, count( mid ) AS NOS
FROM meals, parents, pupils
WHERE meals.school_id =46
AND (
meals.date
BETWEEN “20110102”
AND “20110108”
)
AND meals.parent_id = parents.id AND
typeCode=‘2’ AND meals.pupil_id=pupils.id AND pupils.year!=‘9’
UNION is an option, but not the best one… better just to widen your search criteria to pull all your options in one simple query… try this:
SELECT concat( '£', format( sum( price ) , 2 ) ) AS CPM, count( mid ) AS NOS
FROM
meals
INNER JOIN parents ON meals.parent_id = parents.id
INNER JOIN pupils ON meals.pupil_id = pupils.id
WHERE
meals.school_id =46
AND meals.date BETWEEN "20110102" AND "20110108"
AND ((typeCode = '1' AND pupils.year IN ('3','9'))
OR (typeCode = '2' AND pupils.year = '9'));
Thanks for the suggestion. All the 4 queries return its own result depend upon their criteria. i want the data result in one row as follows i.e., A, Nos, B, Nos, C, Nos, D, Nos. thank you
transio, have another look – your query returns only one sum and one count, whereas the original queries produce four different sets of sums and counts
SELECT
CONCAT( '£', FORMAT( SUM(CASE WHEN typeCode = '1' AND pp.year != '9' THEN price ELSE 0 END),2)) AS CPMA,
COUNT(CASE WHEN typeCode = '1' AND pp.year != '9' THEN 1 ELSE NULL END) AS NOSA,
CONCAT( '£', FORMAT( SUM(CASE WHEN typeCode = '1' AND pp.year = '9' THEN price ELSE 0 END),2)) AS CPMB,
COUNT(CASE WHEN typeCode = '1' AND pp.year = '9' THEN 1 ELSE NULL END) AS NOSB,
CONCAT( '£', FORMAT( SUM(CASE WHEN typeCode = '1' AND pp.year = '3' THEN price ELSE 0 END),2)) AS CPMC,
COUNT(CASE WHEN typeCode = '1' AND pp.year = '3' THEN 1 ELSE NULL END) AS NOSC,
CONCAT( '£', FORMAT( SUM(CASE WHEN typeCode = '2' AND pp.year != '9' THEN price ELSE 0 END),2)) AS CPMD,
COUNT(CASE WHEN typeCode = '2' AND pp.year != '9' THEN 1 ELSE NULL END) AS NOSD
FROM
meals AS m
INNER JOIN parents AS p ON m.parent_id = p.id
INNER JOIN pupils AS pp ON m.pupil_id = pp.id
WHERE
m.school_id =46
AND m.date BETWEEN "20110102" AND "20110108"
GROUP BY m.id;
How to sum the three quantity QTYA+QTYB+QTYC?. I tried as follow.
SELECT s.name,
COUNT(CASE WHEN typeCode = ‘1’ AND pp.year != ‘9’ THEN 1 ELSE NULL END) AS QTYA,
CONCAT( ‘£’, FORMAT( SUM(CASE WHEN typeCode = ‘1’ AND pp.year != ‘9’ THEN price ELSE 0 END),2)) AS STD,
COUNT(CASE WHEN typeCode = '1' AND pp.year = '9' THEN 1 ELSE NULL END) AS QTYB,
CONCAT( '£', FORMAT( SUM(CASE WHEN typeCode = '1' AND pp.year = '9' THEN price ELSE 0 END),2)) AS NRY,
COUNT(CASE WHEN typeCode in ('3') THEN 1 ELSE NULL END) AS NOSC,
CONCAT( '£', FORMAT( SUM(CASE WHEN typeCode IN ('3') THEN price ELSE 0 END),2)) AS ADT,
COUNT(CASE WHEN typeCode = '2' AND pp.year != '9' THEN 1 ELSE NULL END) AS QTYC,
CONCAT( '£', FORMAT( SUM(CASE WHEN typeCode = '2' AND pp.year != '9' THEN price ELSE 0 END),2)) AS FREEADT,
SUM(QTYA+QTYB+QTYC)
FROM
meals AS m
INNER JOIN parents AS p ON m.parent_id = p.id
INNER JOIN pupils AS pp ON m.pupil_id = pp.id
INNER JOIN schools AS s ON m.school_id = s.id
WHERE
– m.school_id =4 AND
m.date BETWEEN “20110102” AND “20110108”
GROUP BY S.NAME
SELECT s.name,
COUNT(CASE WHEN typeCode = ‘1’ AND pp.year != ‘9’ THEN 1 ELSE NULL END) AS QTYA,
CONCAT( ‘£’, FORMAT( SUM(CASE WHEN typeCode = ‘1’ AND pp.year != ‘9’ THEN price ELSE 0 END),2)) AS STD,
COUNT(CASE WHEN typeCode = '1' AND pp.year = '9' THEN 1 ELSE NULL END) AS QTYB,
CONCAT( '£', FORMAT( SUM(CASE WHEN typeCode = '1' AND pp.year = '9' THEN price ELSE 0 END),2)) AS NRY,
COUNT(CASE WHEN typeCode in ('3') THEN 1 ELSE NULL END) AS NOSC,
CONCAT( '£', FORMAT( SUM(CASE WHEN typeCode IN ('3') THEN price ELSE 0 END),2)) AS ADT,
COUNT(CASE WHEN typeCode = '2' AND pp.year != '9' THEN 1 ELSE NULL END) AS QTYC,
CONCAT( '£', FORMAT( SUM(CASE WHEN typeCode = '2' AND pp.year != '9' THEN price ELSE 0 END),2)) AS FREEADT,
QTYA+QTYB+QTYC AS total
FROM
meals AS m
INNER JOIN parents AS p ON m.parent_id = p.id
INNER JOIN pupils AS pp ON m.pupil_id = pp.id
INNER JOIN schools AS s ON m.school_id = s.id
WHERE
– m.school_id =4 AND
m.date BETWEEN “20110102” AND “20110108”
GROUP BY S.NAME
SELECT s.name,
COUNT(CASE WHEN typeCode = ‘1’ AND pp.year != ‘9’ THEN 1 ELSE NULL END) AS QTYA,
CONCAT( ‘£’, FORMAT( SUM(CASE WHEN typeCode = ‘1’ AND pp.year != ‘9’ THEN price ELSE 0 END),2)) AS STD,
COUNT(CASE WHEN typeCode = ‘1’ AND pp.year = ‘9’ THEN 1 ELSE NULL END) AS QTYB,
CONCAT( ‘£’, FORMAT( SUM(CASE WHEN typeCode = ‘1’ AND pp.year = ‘9’ THEN price ELSE 0 END),2)) AS NRY,
COUNT(CASE WHEN typeCode in (‘3’) THEN 1 ELSE NULL END) AS NOSC,
CONCAT( ‘£’, FORMAT( SUM(CASE WHEN typeCode IN (‘3’) THEN price ELSE 0 END),2)) AS ADT,
COUNT(CASE WHEN typeCode = ‘2’ AND pp.year != ‘9’ THEN 1 ELSE NULL END) AS QTYC,
CONCAT( ‘£’, FORMAT( SUM(CASE WHEN typeCode = ‘2’ AND pp.year != ‘9’ THEN price ELSE 0 END),2)) AS FREEADT,
COUNT(CASE WHEN typeCode = ‘1’ AND pp.year != ‘9’ THEN 1 ELSE NULL END)
+
COUNT(CASE WHEN typeCode = ‘1’ AND pp.year = ‘9’ THEN 1 ELSE NULL END)
+
COUNT(CASE WHEN typeCode in (‘3’) THEN 1 ELSE NULL END)
+
COUNT(CASE WHEN typeCode = ‘2’ AND pp.year != ‘9’ THEN 1 ELSE NULL END)
AS TOTAL
FROM
meals AS m
INNER JOIN parents AS p ON m.parent_id = p.id
INNER JOIN pupils AS pp ON m.pupil_id = pp.id
INNER JOIN schools AS s ON m.school_id = s.id
WHERE
– m.school_id =4 AND
m.date BETWEEN “20110102” AND “20110108”
GROUP BY S.NAME