CODEWITHSUNDEEP
sql-server-2008t-sql
Derfla Smith
Derfla Smith

Reputation: 37

SQL Server: count transaction

I have this table in SQL Server:

table

I want a result like this

enter image description here

I am going to write SQL queries to count the transaction and consolidate every month.

Thank you in advance.

Upvotes: 1

Views: 301

Answers (3)

rcs
rcs

Reputation: 7197

You can use GROUP BY clause to count the transaction. Assuming your TXN Date is of date type, you can use following query:

SELECT    CONVERT(VARCHAR(6), TXN_DATE, 112) AS YYYYMM, COUNT(*) AS TXN_COUNT
FROM      MyTable
GROUP BY  CONVERT(VARCHAR(6), TXN_DATE, 112)
ORDER BY  CONVERT(VARCHAR(6), TXN_DATE, 112)

EDIT: since your TXN_DATE is int type, you can use the following

SELECT    LEFT(CONVERT(VARCHAR, TXN_DATE), 6) AS YYYYMM, COUNT(*) AS TXN_COUNT
FROM      MyTable
GROUP BY  LEFT(CONVERT(VARCHAR, TXN_DATE), 6)
ORDER BY  LEFT(CONVERT(VARCHAR, TXN_DATE), 6)

Upvotes: 0

Sandip - Frontend Developer
Sandip - Frontend Developer

Reputation: 14669

Use Group By and Substring :

SELECT 
    SUBSTRING(CAST(TXNDate AS VARCHAR(12)),0,9) AS TXNDate,COUNT(*) AS 'TXN Count'
FROM
@tblTest
GROUP BY SUBSTRING(CAST(TXNDate AS VARCHAR(12)),0,9)

Upvotes: 0

neer
neer

Reputation: 4082

First, you need to edit the column you want to group.

SELECT
    A.YYYYMM,
    COUNT(*) TxnCount
FROM
(
    SELECT
        *,
        LEFT(TXN_DATE, 6) YYYYMM
    FROM
        Tbl
) A
GROUP BY
    A.YYYYMM

Upvotes: 1

Related Questions