CODEWITHSUNDEEP
sqlsql-servert-sql
Xion
Xion

Reputation: 494

Sum up multiple values in SQL

I'm using the following SQL code to get the count of reservation for each quater of the year:

SELECT 
    Year, 
    ISNULL(QPivot.[1],0) As [Quater 1], 
    ISNULL(QPivot.[2],0) As [Quater 2], 
    ISNULL(QPivot.[3],0) As [Quater 3], 
    ISNULL(QPivot.[4],0) As [Quater 4] 
FROM 
    (SELECT 
         YEAR(checkInDate) [Year], 
         DATEPART(QUARTER, checkInDate) [Quarter], 
         COUNT(1) [Reservation Count] 
     FROM 
         ReservationRoom  
     GROUP BY 
         YEAR(checkInDate), DATEPART(QUARTER, checkInDate)) AS QuarterlyData 
PIVOT(SUM([Reservation Count]) 
FOR QUARTER IN ([1],[2],[3],[4])) AS QPivot

This is the results I got from that query:

[my result

But now I want to add in one column [Total] which sum up all the values of all quarters for the year. For example year 2015 is 1 and 2016 is 8.

Can anyone help me with this?

Upvotes: 1

Views: 144

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

I generally find pivot queries a bit hard to read. All that your query does is have a result row per year (so group by year) and count quarters and total (so have some count expressions). The query can easily be written as:

select 
  year(checkindate) as year, 
  count(case when datepart(quarter, checkindate) = 1 then 1 end) as q1,
  count(case when datepart(quarter, checkindate) = 2 then 1 end) as q2,
  count(case when datepart(quarter, checkindate) = 3 then 1 end) as q3,
  count(case when datepart(quarter, checkindate) = 4 then 1 end) as q4,
  count(*) as total
from reservationroom  
group by year(checkindate)
order by year(checkindate);

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

Try adding the four quarters together:

SELECT t.Year, t.[Quarter 1], t.[Quarter 2], t.[Quarter 3], t.[Quarter 4],
    (t.[Quarter 1] + t.[Quarter 2] + t.[Quarter 3] + t.[Quarter 4]) AS Total
FROM
(
    SELECT Year, ISNULL(QPivot.[1],0) As [Quater 1], ISNULL(QPivot.[2],0) As [Quater 2],
        ISNULL(QPivot.[3],0) As [Quater 3], ISNULL(QPivot.[4],0) As [Quater 4],
    FROM
    (
        SELECT YEAR(checkInDate) [Year], DATEPART(QUARTER, checkInDate) [Quarter], COUNT(1) [Reservation Count] 
        FROM ReservationRoom  
        GROUP BY YEAR(checkInDate), DATEPART(QUARTER,checkInDate)) AS QuarterlyData
        PIVOT( SUM([Reservation Count]) 
        FOR QUARTER IN ([1],[2],[3],[4])
    ) AS QPivot
) t

Upvotes: 1

Related Questions