I have these two tables:

![two tables](https://i.stack.imgur.com/lO0Sk.png)

and I want get this result:

![result](https://i.stack.imgur.com/svU7j.png)

How can I achieve this by using only one query?

I tried with join and count and group by but I cannot get it right.

I tried this already, but I cannot get it to work properly.

```
SELECT
    coupon.*,
    couponUsers.returned AS COUPON_TOTAL_USERS,
    couponUses.returned AS COUPON_TOTAL_USES
FROM
    coupon,
    (SELECT
         coupon.COUPON_CODE,
         COUNT(redeemed.REDEEMED_USER) AS returned
     FROM
         coupon
     JOIN 
         redeemed ON coupon.COUPON_CODE = redeemed.REDEEMED_CODE
     GROUP BY
         redeemed.REDEEMED_USER) couponUsers,
    (SELECT
         coupon.COUPON_CODE,
         COUNT(redeemed.REDEEMED_CODE) AS returned
     FROM
         coupon
     JOIN 
         redeemed ON coupon.COUPON_CODE = redeemed.REDEEMED_CODE
     GROUP BY
         redeemed.REDEEMED_CODE) couponUses
WHERE 
    coupon.COUPON_CODE = couponUsers.COUPON_CODE 
    AND coupon.COUPON_CODE = couponUses.COUPON_CODE
GROUP BY
    coupon.COUPON_CODE
ORDER BY
    coupon.COUPON_ID ASC

```

This is the build schema if you want to try it yourself in SQL fiddle or something like that..

```
CREATE TABLE IF NOT EXISTS `coupon` 
(
    `COUPON_ID` int(11) NOT NULL,
    `COUPON_CODE` varchar(32) NOT NULL
) DEFAULT CHARSET=utf8;

INSERT INTO `coupon` (`COUPON_ID`, `COUPON_CODE`) VALUES 
(1, "AAAAA"),
(2, "BBBBB"),
(3, "CCCCC"),
(4, "DDDDD"),
(5, "EEEEE");

CREATE TABLE IF NOT EXISTS `redeemed` (
  `REDEEMED_ID` int(11) NOT NULL,
  `REDEEMED_USER` varchar(32) NOT NULL,
  `REDEEMED_CODE` varchar(32) NOT NULL
) DEFAULT CHARSET=utf8;
INSERT INTO `redeemed` (`REDEEMED_ID`, `REDEEMED_USER`, `REDEEMED_CODE`) VALUES 
(1, "TOM", "AAAAA"),
(2, "PAULA", "BBBBB"),
(3, "TOBI", "CCCCC"),
(4, "JANA", "DDDDD"),
(5, "INGO", "EEEEE"),
(6, "TOM", "AAAAA"),
(7, "PETER", "EEEEE"),
(8, "JIM", "DDDDD"),
(9, "SARA", "AAAAA"),
(10, "TOBI", "CCCCC"),
(11, "PAULA", "AAAAA"),
(12, "TOM", "AAAAA"),
(13, "PAULA", "BBBBB"),
(14, "JIM", "DDDDD"),
(15, "JANA", "DDDDD");

```

i am trying this already a couple hours..
its time for some help ^^