SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Addict
    Join Date
    Mar 2011
    Location
    Manchester, UK
    Posts
    226
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    PHP & MySQL - MySQL Group Help

    Hi Guys,

    Hope you are all well. I'm trying to fix a problem but I'm unable too. Below is my MySQL Query:

    Code:
    SELECT
        ProgramName  AS `name`,
    	AffID AS 'aff',
    	SUM(CASE WHEN Status = 'Pending' THEN 1 ELSE 0 END) AS `pending_leads`,
    	SUM(CASE WHEN Status = 'Approved' THEN 1 ELSE 0 END) AS `confirmed_leads`,
    	SUM(Payout) AS `payout`,
    	SUM(NetPayout) AS `netpayout`,
    	SUM(CASE WHEN Status = 'Pending' THEN Payout ELSE 0 END) AS `pending`,
    	SUM(CASE WHEN Status = 'Approved' THEN Payout ELSE 0 END) AS `approved`,
    	SUM(CASE WHEN Status = 'Pending' THEN NetPayout ELSE 0 END) AS `netpending`,
    	SUM(CASE WHEN Status = 'Approved' THEN NetPayout ELSE 0 END) AS `netapproved`
    	FROM affleads
    	WHERE `Date` LIKE '%2011%'
    	GROUP BY AffID;
    Now that will display all the records in the database and group them by AffID. Now what i wanted to happen was to Group them together by AffID and the ProgramName but its just grouping them by AffID and placing all the leads under the same PRogramName even if its not the same ProgramName, so i then changed the Query to the following:

    Code:
    SELECT
        ProgramName  AS `name`,
    	AffID AS 'aff',
    	SUM(CASE WHEN Status = 'Pending' THEN 1 ELSE 0 END) AS `pending_leads`,
    	SUM(CASE WHEN Status = 'Approved' THEN 1 ELSE 0 END) AS `confirmed_leads`,
    	SUM(Payout) AS `payout`,
    	SUM(NetPayout) AS `netpayout`,
    	SUM(CASE WHEN Status = 'Pending' THEN Payout ELSE 0 END) AS `pending`,
    	SUM(CASE WHEN Status = 'Approved' THEN Payout ELSE 0 END) AS `approved`,
    	SUM(CASE WHEN Status = 'Pending' THEN NetPayout ELSE 0 END) AS `netpending`,
    	SUM(CASE WHEN Status = 'Approved' THEN NetPayout ELSE 0 END) AS `netapproved`
    	FROM affleads
    	WHERE `Date` LIKE '%2011%'
    	GROUP BY AffID AND ProgramName;
    But thats grouping everything to the same AffID and same ProgramName.

    So how do i do it so it displays each ProgramName for each AffID that it finds with the total pending, approved etc... using the query above?

    Any help would be great.

    Thank you.
    You're Help Does Not Go Unnoticed, I have So Far Donated 25 GBP
    To Cancer Research UK As A Thank You To All The SitePoint
    Members That Have Helped Me In The PHP Forum Thank You!

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,396
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Code:
    GROUP BY ProgramName, AffID

  3. #3
    SitePoint Addict
    Join Date
    Mar 2011
    Location
    Manchester, UK
    Posts
    226
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you Guido, i was on the right lines just needed to replace AND with a ,

    Thank you again.
    You're Help Does Not Go Unnoticed, I have So Far Donated 25 GBP
    To Cancer Research UK As A Thank You To All The SitePoint
    Members That Have Helped Me In The PHP Forum Thank You!


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
  •