SQL SELECT multiple count in one query

i have these two tables:



and i want get this as result:



how can i archive this by using only one query? i tried with join and count and group by but i cannot get it.

i tried this alread, 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 somthing 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 ^^

Continue reading...