Hi Guys,
Hope you are all well. I’m trying to fix a problem but I’m unable too. Below is my MySQL Query:
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:
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.
