Reputation: 151
Right now I have the following query:
SELECT
rm.reward_name,
rm.rewardid,
rc.reward_code,
rc.status,
rc.rewardid,
rc.add_date,
rc.status
from rewards_codes as rc
INNER JOIN reward_mast as rm on rc.rewardid on rm.rewardid
where DATE(rc.add_date) between '2012-03-16' AND '2013-03-16';
I want to fetch total no of codes,available codes from all codes,used codes
i have taken status field in rewards_codes field for differentiate code status
0 - Available to use
1- Used code
So my final output should be like following:
-----------------------------------------------------------
Reward Name Total Codes Available code Used code
my_reward 100 40 60
extra_reward 100 90 10
-----------------------------------------------------------
[Update]
Here is some sample data from both table...
reward_mast
rewardid rewrd_name
1 my_reward
2 extra_reward
3 test_reward
rewards_codes
codeId rewardid reward_code add_date status
1 1 aka454 2012-11-21 0
2 2 ala499 2012-04-21 0
3 1 pao789 2012-08-21 0
4 3 zlk753 2012-01-21 0
5 2 qra954 2012-05-21 0
Upvotes: 0
Views: 151
Reputation: 79979
Try this:
SELECT
rm.rewardid,
rm.reward_name,
IFNULL(COUNT(rc.reward_code), 0) AS 'Total Codes',
IFNULL(SUM(rc.status = 0), 0) AS 'Available code',
IFNULL(SUM(rc.status = 1), 0) AS 'Used Codes'
FROM reward_mast as rm
LEFT JOIN rewards_codes as rc on rc.rewardid = rm.rewardid
WHERE DATE(rc.add_date) between '2012-03-16' AND '2013-03-16'
GROUP BY rm.reward_name,
rm.rewardid;
This will give you the count of each category of status codes individually, Totalcodes
, Available Codes
and Used Codes
.
This will give you:
| REWARDID | REWARD_NAME | TOTAL CODES | AVAILABLE CODE | USED CODES |
-----------------------------------------------------------------------
| 1 | my_reward | 2 | 2 | 0 |
| 2 | extra_reward | 2 | 2 | 0 |
Upvotes: 1