CODEWITHSUNDEEP
sqlms-accesscount
NickA
NickA

Reputation: 11

Multiple Counts (of transaction type) in single SQL Query

So I have a table for all transactions of a business, very simply it has a payment method column that lists how all the transactions were conducted, Cash, Credit or Debit.

What I want to do is create with the query two columns that count out how each transaction was completed, one column being Cash, the other being Card which would include all Credit and Debit purchases.

Here's what I have so far:

SELECT COUNT (*) as CashTransactions
FROM Transaction
WHERE PaymentMethod='Cash'

And what I want the output to be is 

Cash Transactions  |       Card Transactions

 (Count of Cash)   |  (Count of Credit AND Debit)

Upvotes: 1

Views: 1896

Answers (3)

Fauzi88
Fauzi88

Reputation: 713

If you use sql server, you can try this query:

SELECT 
   SUM(CASE WHEN PaymentMethod='Cash' THEN 1 ELSE 0 END) as CashTransaction,
   SUM(CASE WHEN PaymentMethod IN ('Credit','Debit') THEN 1 ELSE 0 END) as CardTransaction 
FROM Transaction

But, if you use ms-access, in query, you can try this query:

SELECT sum(IIF(PaymentMethod='Cash',1,0)) as CashTransaction, sum(IIF(PaymentMethod in ('Credit','Debit'),1,0)) as CardTransaction
FROM Transaction;

Upvotes: 1

Gustav
Gustav

Reputation: 55816

Just sum up instead of count:

SELECT 
    ABS(SUM(PaymentMethod = 'Cash')) AS CashTransactions,
    ABS(SUM(PaymentMethod <> 'Cash')) AS CardTransactions
FROM 
    Transaction

Upvotes: 0

JCro
JCro

Reputation: 696

Couple of options here. I strongly suggest option one, it's cleaner and faster.

Option 1:

SELECT PaymentMethod, COUNT(*) AS NumberOfTransactions
FROM Transaction
GROUP BY PaymentMethod

Resulting in:

PaymentMethod | NumberOfTransactions
------------------------------------
Cash          | 33
Credit        | 12
Debit         | 87

Or Option 2:

SELECT (
SELECT COUNT(*) AS NumberOfTransactions
FROM Transaction
WHERE PaymentMethod = 'Cash'
) as CashTransactions,
(
SELECT COUNT(*) AS NumberOfTransactions
FROM Transaction
WHERE NOT PaymentMethod = 'Cash'
) as OtherTransactions

Resulting in:

CashTransactions| OtherTransactions
------------------------------------
33              | 99

Upvotes: 1

Related Questions