How to make it into a single query?

stephenvs · March 3, 2011, 2:52pm

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’

r937 · March 3, 2011, 3:02pm

tip: use UNION to combine them

system · March 3, 2011, 3:25pm

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'));

stephenvs · March 3, 2011, 3:29pm

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

r937 · March 3, 2011, 3:35pm

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

system · March 3, 2011, 3:42pm

I see… Could be accomplished with a GROUP, but would have to see the schema to know what’s what.

system · March 3, 2011, 3:50pm

Here, try this:


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;

r937 · March 3, 2011, 3:57pm

that’s the ticket… except without the GROUP BY

stephenvs · March 3, 2011, 4:06pm

wow, marvellous. thanks. i really need to study a lot.

system · March 3, 2011, 4:29pm

LOL, right… I had put that in before and forgot about it.

stephenvs · March 3, 2011, 5:13pm

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

r937 · March 3, 2011, 5:18pm

by wrapping that query with an outer query

SELECT *
     , QTYA+QTYB+QTYC AS total
  FROM (
              [i]existing query here[/i]
       ) AS dt

stephenvs · March 3, 2011, 5:28pm

#1054 - Unknown column ‘QTYA’ in ‘field list’

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

stephenvs · March 3, 2011, 5:49pm

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