CODEWITHSUNDEEP
mysqlsqljoinsuminner-join
Mark Martin
Mark Martin

Reputation: 151

MYSQL Inner join with select statement

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

Answers (1)

Mahmoud Gamal
Mahmoud Gamal

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.

SQL Fiddle Demo

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

Related Questions