SQL SELECT multiple count in one query

Question

I have these two tables:

two tables

and I want get this result:

result

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 ^^

Hey there, try posting some programming code, it'll boost your chances on receiving answers. — ARLCode
– ARLCode, Commented Apr 22, 2021 at 22:42
You can use a join query to join COUPON_CODE and REDEEMED_CODE and do the count on the other columns. Please add tables in text format if you want more helpful answers please also code you're tried as well — Prav
– Prav, Commented Apr 22, 2021 at 22:45
@PraveenPremaratne i added tables (build schema) and my wasted code ^^ — ThirtySix
– ThirtySix, Commented Apr 22, 2021 at 23:11

marc_s · Accepted Answer · 2021-04-23 04:24:16Z

1

You should be able to generate the wanted counts in a single table query:

select 
       redeemed_code
     , count(*) as tot_uses
     , count(distinct redeemed_user) as tot_users
from redeemed
group by redeemed_code

You can join this to the coupon table for final output, for example with a left join you would get all coupons listed in coupon even if none have been redeemed yet.

 select
      c.coupon_id
    , c.coupon_code
    , coalesce(d.tot_uses,0) as tot_uses
    , coalesce(d.tot_users,0) as tot_users
from coupon as c
left join (
         select 
                 redeemed_code
              ,  count(*) as tot_uses
              , count(distinct redeemed_user) as tot_users
         from redeemed
         group by redeemed_code
         ) as d on c.coupon_code = d.redeemed_code

coupon_id	coupon_code	tot_uses	tot_users
1	AAAAA	5	3
2	BBBBB	2	1
3	CCCCC	2	1
4	DDDDD	4	2
5	EEEEE	2	2

db<>fiddle here

edited Apr 23, 2021 at 4:24

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Apr 22, 2021 at 23:19

Paul Maxwell

35.7k4 gold badges39 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ThirtySix Over a year ago

i tryed your code but this is not working.

Paul Maxwell Over a year ago

wrong column referenced, sorry was trying to answer using a phone

ThirtySix Over a year ago

thanks alot, thats so helpful and working perfect.

Gautham Acharya · Accepted Answer · 2021-04-22 23:39:42Z

0

This is for PostgreSQL, but this query should work for you. You can do the GROUP BY on the redeemed table and join that with coupon to get the COUPON_ID. \

psql=# WITH redeemed_query AS (
        SELECT 
                "REDEEMED_CODE", 
                COUNT(*) AS "TOTAL_USES", 
                COUNT(DISTINCT("REDEEMED_USER")) AS "TOTAL_USERS" 
        from redeemed GROUP BY "REDEEMED_CODE"
) 
SELECT * FROM redeemed_query 
INNER JOIN 
coupon ON redeemed_query."REDEEMED_CODE" = coupon."COUPON_CODE";


 REDEEMED_CODE | TOTAL_USES | TOTAL_USERS | COUPON_ID | COUPON_CODE 
---------------+------------+-------------+-----------+-------------
 AAAAA         |          5 |           3 |         1 | AAAAA
 BBBBB         |          2 |           1 |         2 | BBBBB
 CCCCC         |          2 |           1 |         3 | CCCCC
 DDDDD         |          4 |           2 |         4 | DDDDD
 EEEEE         |          2 |           2 |         5 | EEEEE
(5 rows)

answered Apr 22, 2021 at 23:39

Gautham Acharya

1632 silver badges9 bronze badges

2 Comments

ThirtySix Over a year ago

thank you, i dont use postgreSQL but maybe this is also helpful for someone else in future :)

Gautham Acharya Over a year ago

The query should still work for you, the quotation marks should just be replace by tick marks ` .

Collectives™ on Stack Overflow

SQL SELECT multiple count in one query

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related