SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Enthusiast
    Join Date
    Sep 2004
    Location
    Scotland
    Posts
    40
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question How to make it into a single query?

    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'

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    tip: use UNION to combine them
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

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

  4. #4
    SitePoint Enthusiast
    Join Date
    Sep 2004
    Location
    Scotland
    Posts
    40
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I see... Could be accomplished with a GROUP, but would have to see the schema to know what's what.

  7. #7
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here, try this:
    Code:
    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;

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by transio View Post
    Here, try this:
    that's the ticket... except without the GROUP BY

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Enthusiast
    Join Date
    Sep 2004
    Location
    Scotland
    Posts
    40
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Arrow

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

  10. #10
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    that's the ticket... except without the GROUP BY

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

  11. #11
    SitePoint Enthusiast
    Join Date
    Sep 2004
    Location
    Scotland
    Posts
    40
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Last edited by stephenvs; Mar 3, 2011 at 11:17. Reason: forget the title

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by stephenvs View Post
    How to sum the three quantity QTYA+QTYB+QTYC?
    by wrapping that query with an outer query
    Code:
    SELECT *
         , QTYA+QTYB+QTYC AS total
      FROM (
                  existing query here
           ) AS dt
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Enthusiast
    Join Date
    Sep 2004
    Location
    Scotland
    Posts
    40
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    #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

  14. #14
    SitePoint Enthusiast
    Join Date
    Sep 2004
    Location
    Scotland
    Posts
    40
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •