CODEWITHSUNDEEP
sql
chendriksen
chendriksen

Reputation: 1024

SQL using same field twice with different criteria

I've got a header for count(voucher_credits.voucher_id), I'm already using a WHERE statement to add it into my report, but I'd like to add it again as another column using different criteria, how can I do this?

SELECT vouchers.code, vouchers.credit, count(voucher_credits.voucher_id) as Activated
FROM vouchers, voucher_credits, users
WHERE vouchers.id = voucher_credits.voucher_id
    AND voucher_credits.remaining_credit = 0
    AND users.id = voucher_credits.user_id AND users.state = 'active'
    AND vouchers.is_customer_service = FALSE
GROUP by vouchers.code, vouchers.credit
ORDER by count(voucher_credits.voucher_id) DESC
LIMIT 100

The second column would be users.state = 'cancelled' without the .remaining_credit = 0

Upvotes: 2

Views: 4708

Answers (3)

Szymon
Szymon

Reputation: 43023

You can join the table twice. I used FULL OUTER JOIN in case you get different records from the 2 subqueries. Feel free to change the join type.

select t1.code, t1.credit, t1.Activated, t2.code, t2.credit, t2.Cancelled
from
(SELECT vouchers.code, vouchers.credit, count(voucher_credits.voucher_id) as Activated
FROM vouchers, voucher_credits, users
WHERE vouchers.id = voucher_credits.voucher_id
    AND voucher_credits.remaining_credit = 0
    AND users.id = voucher_credits.user_id AND users.state = 'active'
    AND vouchers.is_customer_service = FALSE
GROUP by vouchers.code, vouchers.credit) t1
FULL OUTER JOIN
(SELECT vouchers.code, vouchers.credit, count(voucher_credits.voucher_id) as Cancelled
FROM vouchers, voucher_credits, users
WHERE vouchers.id = voucher_credits.voucher_id
    AND users.state = 'cancelled'
    AND users.id = voucher_credits.user_id AND users.state = 'active'
    AND vouchers.is_customer_service = FALSE
GROUP by vouchers.code, vouchers.credit) t2
ON t1.code = t2.code and t1.credit = t2.credit

Upvotes: 1

lc.
lc.

Reputation: 116458

If all data come from the same row, you can use a "conditional sum" count, which is basically where you add 0 or 1 based on an expression:

SELECT ...,
  SUM(CASE WHEN voucher_credits.remaining_credit = 0 AND users.state = 'active'
       THEN 1 ELSE 0 END) Activated,
  SUM(CASE WHEN users.state = 'cancelled' THEN 1 ELSE 0 END) CanceledUser,
FROM ...
...

Using your query, this would be:

SELECT vouchers.code, vouchers.credit,
  SUM(CASE WHEN voucher_credits.remaining_credit = 0 AND users.state = 'active'
       THEN 1 ELSE 0 END) Activated,
  SUM(CASE WHEN users.state = 'cancelled' THEN 1 ELSE 0 END) CanceledUser,
FROM vouchers
INNER JOIN voucher_credits ON vouchers.id = voucher_credits.voucher_id
INNER JOIN users ON users.id = voucher_credits.user_id
WHERE vouchers.is_customer_service = FALSE
GROUP by vouchers.code, vouchers.credit
ORDER by Activated DESC
LIMIT 100

Upvotes: 1

Edper
Edper

Reputation: 9322

Try UNION ALL:

SELECT vouchers.code, vouchers.credit, count(voucher_credits.voucher_id) as Activated
FROM vouchers, voucher_credits, users
WHERE vouchers.id = voucher_credits.voucher_id
  AND voucher_credits.remaining_credit = 0
  AND users.id = voucher_credits.user_id AND users.state = 'active'
  AND vouchers.is_customer_service = FALSE
GROUP by vouchers.code, vouchers.credit
ORDER by count(voucher_credits.voucher_id) DESC
UNION ALL
SELECT vouchers.code, vouchers.credit, count(voucher_credits.voucher_id) as Activated
FROM vouchers, voucher_credits, users
WHERE vouchers.id = voucher_credits.voucher_id
  AND users.state = 'cancelled' 
  AND users.id = voucher_credits.user_id AND users.state = 'active'
  AND vouchers.is_customer_service = FALSE
GROUP by vouchers.code, vouchers.credit
ORDER by count(voucher_credits.voucher_id) DESC

Upvotes: 1

Related Questions