CODEWITHSUNDEEP
sqlsql-server
Jignesh Shah
Jignesh Shah

Reputation: 1

SQL Server - TSQL query statement

I want to prepared following summary report of my table -

Table name : Order  
Fields Name : Orderdate,Repname,Storename,salesamount

output :

Store Name    Jan    Feb    Mar .... Dec   Total
ABC           50     10     20  .....      80
XYZ           30     NULL   20             50

Upvotes: 0

Views: 54

Answers (1)

Henry Lu
Henry Lu

Reputation: 381

You can use Sql Server's PIVOT functionality to do this. Alternatively you can use aggregates with case when statements

Simplified Example SQL using aggregates with case when statements (Note that the below sql has a bug where it will sum the sales amounts from Jan 2015 and Jan 2016 together if the table has enough data. It is only there to demonstrate the concept of how the result could be achieved)

SELECT
    StoreName,
    SUM(CASE WHEN MONTH(Orderdate) = 1 THEN salesamount ELSE NULL END) as Jan,
    SUM(CASE WHEN MONTH(Orderdate) = 2 THEN salesamount ELSE NULL END) as Feb,
    .
    .
    .
    SUM(CASE WHEN MONTH(Orderdate) = 12 THEN salesamount ELSE NULL END) as Dec,
    SUM(salesamount) as Total
FROM
    Order  
GROUP BY
    StoreName

Upvotes: 1

Related Questions