title: Count multiple rows in SQL Server Query
body: <p>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?</p>
  
  <pre><code>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    
  </code></pre>
  
  <p>And I get these results:</p>
  
  <pre><code>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           
  </code></pre>
  
  <p>As you can see the results are repeated, wheres in the table it should be different values.<br>
  Any ideas?</p>
  
  <hr>
  
  <p>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</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Query looks like this:</p>
  
  <pre><code>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
  </code></pre>