CODEWITHSUNDEEP
sqlsql-server
Naveed Ullah
Naveed Ullah

Reputation: 621

Sum of rows by year with total

I want to sum rows by year plus get total at last column as shown in table and if possible also show total at the end row. I tried my best but could not get the desired result. This is what I am trying. 

SELECT city
    ,[date]
    ,sum(price) AS Total
FROM [payer]
GROUP BY city
    ,[date]

+----------+------+------+-------+
|   City   | 2018 | 2019 | Total |
+----------+------+------+-------+
| New York |    4 |    6 |    10 |
| Toronto  |    8 |   12 |    20 |
| Total    |   12 |   18 |       |
+----------+------+------+-------+

You may create table with data from below code. 

CREATE TABLE [dbo].[payer](
    [Id] [int] NULL,
    [city] [varchar](50) NULL,
    [Date] [varchar](50) NULL,
    [price] [int] NULL
) ON [PRIMARY]
GO

insert into payer values(1,'newyork','2018',2)
insert into payer values(2,'newyork','2018',2)
insert into payer values(3,'newyork','2019',3)
insert into payer values(4,'newyork','2019',3)
insert into payer values(5,'toronto','2018',4)
insert into payer values(6,'toronto','2018',4)
insert into payer values(7,'toronto','2019',6)
insert into payer values(8,'toronto','2019',6)

Upvotes: 0

Views: 640

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271051

You can use grouping sets and conditional aggregation:

select coalesce(city, 'Total'),
       sum(case when [date] = '2018' then price end) as sum_2018,
       sum(case when [date] = '2019' then price end) as sum_2019,
       sum(price) as total
from [payer] p
where [date] in ('2018', '2019')
group by grouping sets ( (city), () );

Here is a db<>fiddle.

First, calling a column date is a bad idea, because that is the name of a data type. It is allowed, but I would recommend a better name.

Second, DATEs should be stored as date -- NOT as a string. It is a really bad practice to store columns with the wrong data type.

In this case, what you call a date is quite misleading, because it is only the year. I would recommend calling the column something like year and defining the type to be an integer.

Upvotes: 4

Suraj Kumar
Suraj Kumar

Reputation: 5653

You could try this using PIVOT as shown below.

;WITH cte
AS (
    SELECT City
        ,[2018]
        ,[2019]
    FROM (
        SELECT city
            ,DATE
            ,total
        FROM (
            SELECT City
                ,DATE
                ,Sum(price) AS Total
            FROM payer
            GROUP BY city
                ,DATE
            ) a
        ) d
    pivot(max(total) FOR DATE IN (
                [2018]
                ,[2019]
                )) piv
    )
SELECT *
FROM cte

UNION

SELECT 'total' AS city
    ,sum([2018]) AS [2018]
    ,sum([2019]) AS [2019]
FROM cte

You can find the live demo here. To get the vertical total you need to write your query as shown below.

SELECT City
    ,[2018]
    ,[2019]
    ,[2018] + [2019] AS [Total]
FROM (
    SELECT *
    FROM cte

    UNION

    SELECT 'total' AS city
        ,sum([2018]) AS [2018]
        ,sum([2019]) AS [2019]
    FROM cte
    ) final

Here is the demo given.

Upvotes: 1

Related Questions