Count multiple rows in SQL Server Query

Question

I have this table called Online_Transaction and i want to show all the types of transactions and how many where made of each type (completed,rejected,pending for authorization) in a specific month. I have this query until now but I get repeated results any ideas?

SELECT DISTINCT
    TRANSACTION_TYPE_ID ,
    YEAR(CREATED_ON) AS YEAR ,
    MONTH(CREATED_ON) AS MONTH ,
    ( SELECT
          Count(TRANSACTION_TYPE_ID)
      FROM
          ONLINE_TRANSACTION
      WHERE
          ONLINE_TRANSACTION.STATUS_ID = 'COMPLETED' AND MONTH(CREATED_ON) = '2' ) AS COMPLETED ,
    ( SELECT
          Count(TRANSACTION_TYPE_ID)
      FROM
          ONLINE_TRANSACTION
      WHERE
          ONLINE_TRANSACTION.STATUS_ID = 'DECLINED' AND MONTH(CREATED_ON) = '2' ) AS DECLINED ,
    ( SELECT
          Count(TRANSACTION_TYPE_ID)
      FROM
          ONLINE_TRANSACTION
      WHERE
          ONLINE_TRANSACTION.STATUS_ID = 'FAILED' AND MONTH(CREATED_ON) = '2' ) AS FAILED ,
    ( SELECT
          Count(TRANSACTION_TYPE_ID)
      FROM
          ONLINE_TRANSACTION
      WHERE
          ONLINE_TRANSACTION.STATUS_ID = 'PENDING_AUTH' AND MONTH(CREATED_ON) = '2' ) AS PENDING_AUTH
--(SELECT Count(*) from )
FROM
    ONLINE_TRANSACTION
WHERE
    MONTH(CREATED_ON) = '2'
GROUP BY
    TRANSACTION_TYPE_ID ,
    ONLINE_TRANSACTION.CREATED_ON

And I get these results:

TRANSACTION_TYPE_ID                  YEAR        MONTH       COMPLETED   DECLINED    FAILED      
------------------------------------ ----------- ----------- ----------- ----------- -------
INSURANCE--TYPE                       2009        2           9712        177         0           
CHEQUEBOOK-TYPE                       2009        2           9712        177         0           
CHEQUE-STOP-YPE                       2009        2           9712        177         0           
PAYMENT-TRANS-TYPE               2009        2           9712        177         0           
DOMESTIC-TRANSFER-TYPE                2009        2           9712        177         0
PAYMENT-TRANS-TYPE                2009        2           9712        177         0           
INTRA-ACCOUNT-TRANS-TYPE              2009        2           9712        177         0           
INTRA-BANK-TRANS-TYPE                 2009        2           9712        177         0           
STANDING-ORDER-TYPE                   2009        2           9712        177         0           
STATEMENT-REORDERING TYPE             2009        2           9712        177         0           
PAYMENTS-TRANS-TYPE             2009        2           9712        177         0

As you can see the results are repeated, wheres in the table it should be different values.
Any ideas?

---

Having tried the answers below this is what I get: this type of results set- it displays different results for each transaction type in different rows so if say the transaction type is Intra-transfer and i has 10 completed and 2 rejected it will show the completed in one row and the rejected in another row? how do your show it in one row per transaction type

TRANSACTION_TYPE_ID        YEAR MONTH REJECTED COMPLETED POSTED

ALPHA-INSURANCE-TRANS-TYPE  2009 2       0 12 0 
CHEQUEBOOK-ORDER-TRANS-TYPE 2009 2      0 0 0 
CHEQUEBOOK-ORDER-TRANS-TYPE 2009 2      0 52 0 
CHEQUEBOOK-ORDER-TRANS-TYPE 2009 2      2 0 0 
CHEQUE-STOP-TRANS-TYPE      2009 2      0 3 0
 PAYMENT-TRANS-TYPE         2009 2     0 361 0 
PAYMENT-TRANS-TYPE          2009 2     1 0 0 
DOMESTIC-TRANSFER-TRANS-TYPE 2009 2    0 0 0 
DOMESTIC-TRANSFER-TRANS-TYPE 2009 2   0 541 0 
DOMESTIC-TRANSFER-TRANS-TYPE 2009 2    6 0 0

Query looks like this:

SELECT DISTINCT
    TRANSACTION_TYPE_ID ,
    YEAR(CREATED_ON) AS YEAR ,
    MONTH(CREATED_ON) AS MONTH ,
    SUM(CASE ONLINE_TRANSACTION.STATUS_ID
          WHEN 'STATUS-TRANS-REJECTED ' THEN 1
          ELSE 0
        END) AS REJECTED ,
    SUM(CASE ONLINE_TRANSACTION.STATUS_ID
          WHEN 'STATUS-TRANS-COMPLETED ' THEN 1
          ELSE 0
        END) AS COMPLETED ,
    SUM(CASE ONLINE_TRANSACTION.STATUS_ID
          WHEN 'STATUS-TRANS-DECLINDED ' THEN 1
          ELSE 0
        END) AS DECLINED ,
    SUM(CASE ONLINE_TRANSACTION.STATUS_ID
          WHEN 'STATUS-TRANS-FAILED' THEN 1
          ELSE 0
        END) AS FAILED ,
    SUM(CASE ONLINE_TRANSACTION.STATUS_ID
          WHEN 'STATUS-TRANS-PENDING-AUTH ' THEN 1
          ELSE 0
        END) AS PENDING_AUTH ,
    SUM(CASE ONLINE_TRANSACTION.STATUS_ID
          WHEN 'STATUS-TRANS-PENDING-POST ' THEN 1
          ELSE 0
        END) AS PENDING_POST ,
    SUM(CASE ONLINE_TRANSACTION.STATUS_ID
          WHEN 'STATUS-TRANS-PENDING' THEN 1
          ELSE 0
        END) AS PENDING ,
    SUM(CASE ONLINE_TRANSACTION.STATUS_ID
          WHEN 'ALPHA-STATUS-TRANS-POSTED' THEN 1
          ELSE 0
        END) AS POSTED
FROM
    ONLINE_TRANSACTION
WHERE
    MONTH(CREATED_ON) = '2'
GROUP BY
    TRANSACTION_TYPE_ID ,
    YEAR(CREATED_ON) ,
    MONTH(CREATED_ON) ,
    STATUS_ID

Answer 1

You need another WHERE clause in your sub queries linking the TRANSACTION_TYPE_IDs up.

SELECT DISTINCT 
  TRANSACTION_TYPE_ID, YEAR(CREATED_ON)AS YEAR, MONTH(CREATED_ON)AS MONTH ,
(SELECT Count(TRANSACTION_TYPE_ID)  FROM ONLINE_TRANSACTION 
 WHERE ONLINE_TRANSACTION.STATUS_ID ='COMPLETED'AND MONTH(CREATED_ON)='2'
    AND TRANSACTION_TYPE_ID=ot.TRANSACTION_TYPE_ID) AS COMPLETED,
(SELECT Count(TRANSACTION_TYPE_ID) FROM ONLINE_TRANSACTION 
 WHERE ONLINE_TRANSACTION.STATUS_ID ='DECLINED'AND MONTH(CREATED_ON)='2'
    AND TRANSACTION_TYPE_ID=ot.TRANSACTION_TYPE_ID) AS DECLINED,
(SELECT Count(TRANSACTION_TYPE_ID) from ONLINE_TRANSACTION
 WHERE  ONLINE_TRANSACTION.STATUS_ID ='FAILED' AND MONTH(CREATED_ON)='2'
    AND TRANSACTION_TYPE_ID=ot.TRANSACTION_TYPE_ID) AS FAILED,
(SELECT Count(TRANSACTION_TYPE_ID) from ONLINE_TRANSACTION
 WHERE  ONLINE_TRANSACTION.STATUS_ID ='PENDING_AUTH'AND MONTH(CREATED_ON)='2'
    AND TRANSACTION_TYPE_ID=ot.TRANSACTION_TYPE_ID) AS PENDING_AUTH
FROM ONLINE_TRANSACTION ot
WHERE MONTH(CREATED_ON)='2'
GROUP BY TRANSACTION_TYPE_ID,ONLINE_TRANSACTION.CREATED_ON

Answer 2

The where clauses in the nested select statements don't distinguish between the different transaction types. The numbers are always the total amounts.

Try looking into sum and case.

TRANSACTION_TYPE_ID, YEAR(CREATED_ON)AS YEAR, MONTH(CREATED_ON)AS MONTH ,
  SUM(CASE ONLINE_TRANSACTION.STATUS_ID WHEN 'COMPLETED' THEN 1 ELSE 0 END) AS COMPLETED,
  SUM(CASE ONLINE_TRANSACTION.STATUS_ID WHEN 'DECLINED' THEN 1 ELSE 0 END) AS DECLINED,
  SUM(CASE ONLINE_TRANSACTION.STATUS_ID WHEN 'FAILED' THEN 1 ELSE 0 END) AS FAILED,
  SUM(CASE ONLINE_TRANSACTION.STATUS_ID WHEN 'PENDING_AUTH' THEN 1 ELSE 0 END) AS PENDING_AUTH
FROM ONLINE_TRANSACTION WHERE MONTH(CREATED_ON)='2'
GROUP BY TRANSACTION_TYPE_ID

Answer 3

Your parenthetical SELECTs in your main SELECT clause go back to the total ONLINE_TRANSACTION table so of course they're getting their counts without regards to the type, but rather overall!

What about changing each of them into a SUM (over the same grouping you already have, so no extra syntax needed) of the STATUS_ID checks -- taking 1 for equality and 0 for difference (that bool to 0/1 int conversion is automatic in many SQL dialects, at worst you'll need an IF or CASE...WHEN or CAST for that, whatever your dialect of choice).

Answer 4

SELECT TRANSACTION_TYPE_ID, YEAR(CREATED_ON) AS YEAR, MONTH(CREATED_ON) AS MONTH , 
SUM(CASE WHEN STATUS_ID = 'COMPLETED' THEN 1 ELSE 0 END) AS Completed,
SUM(CASE WHEN STATUS_ID = 'DECLINED' THEN 1 ELSE 0 END) AS Declined,
SUM(CASE WHEN STATUS_ID = 'FAILED' THEN 1 ELSE 0 END) AS Failed,
SUM(CASE WHEN STATUS_ID = 'PENDING_AUTH' THEN 1 ELSE 0 END) AS Pending_Auth
FROM ONLINE_TRANSACTION
WHERE MONTH(CREATED_ON) = 2
GROUP BY TRANSACTION_TYPE_ID, YEAR(CREATED_ON), MONTH(CREATED_ON)

See if this makes sense.

Answer 5

I tried out the other answers just for my own edification, and thought I'd share the results in case it helps anyone else. The dataset is small, but shows the principle.

sqlite> SELECT * FROM transactions;
id          type_id      status_id
----------  ----------   ----------
1           insurance    completed
2           insurance    declined
3           cheque-stop  completed
4           cheque-stop  completed

sqlite> SELECT
   ...>     type_id,
   ...>     SUM(status_id == 'completed') AS completed,
   ...>     SUM(status_id == 'declined') AS declined
   ...> FROM transactions
   ...> GROUP BY type_id;
type_id      completed   declined
-----------  ----------  ----------
cheque-stop  2           0
insurance    1           1