SQL Server Query Sum By Date And Sum All

Question

I have columns Date, Country and Dollars. I am successful if I query like this picture...

My query is:

select date, country, sum(car), sum(dollar)
group by date, country
union
select '' as All_Date, '' as All_Country, sum(car), sum(dollar)
group by All_Date, All_Country

But I want to result like this...

I confused how i can get totally car and dollars...

By date like at my picture (at my yellow rows)..

Can you help me ?

Thanks your attention..

In the result set screenshot, you have mentioned 'Total' under Date, 'all' under Country etc. Do you want to return the 'exact' same format (including these text) from SQL Server itself? — A.J, Feb 21 '16 at 14:09

score 0 · Answer 1 · answered Feb 21 '16 at 15:39

Assuming you want the 'exact' same format (have the text 'Total' within the Date field etc.) returned from SQL Server, you can use the below query set of queries (Table name assumed here is Tablex):

--STEP 1: Defined a temp table with the grouped data
SELECT --Grouping based on date
    DATE
    ,SUM(CAR) SUM_CAR
    ,SUM(DOLLARS) SUM_DOLLARS
INTO
    #TEMP_GROUPED_DATA
FROM
    TABLEX
GROUP BY
    DATE 



-- STEP 2: Define a temp table with the grouped data and original data
SELECT
    DATE
    ,COUNTRY
    ,CAR
    ,DOLLARS
INTO #GROUPED_DATA2
FROM
    TABLEX
UNION ALL
SELECT
    DATE
    ,'z ALL' --For formatting have added the 'z', which will be removed in the final query
    ,SUM_CAR
    ,SUM_DOLLARS
FROM #TEMP_GROUPED_DATA

--STEP 3: Join the temp table with the main table to return in the required format by using Rank, make some tweaks in renaming column values for sorting out in the exact order
    SELECT
        CASE 
            WHEN COUNTRY LIKE 'Z %' AND DATE IS NOT NULL THEN 'Total' ELSE CASE WHEN DATE IS NULL THEN 'All Date' ELSE
             CONVERT(VARCHAR(255),DATE) END END DATE
        ,CASE WHEN COUNTRY LIKE 'Z %' THEN 'ALL' ELSE COUNTRY END COUNTRY
        ,CAR
        ,DOLLARS
    FROM 
        ( SELECT 
        RANK() over (ORDER  BY DATE,COUNTRY) ROWNO, * FROM  #GROUPED_DATA2 UNION ALL 
    SELECT
        999999 ROWNO
        ,NULL as DATE
        ,'ALL' AS COUNTRY
        ,sum(car)
        ,SUM(DOLLARS)
    FROM
        TABLEX)T
    ORDER BY ROWNO

--Drop temp tables

drop table #GROUPED_DATA2
drop table #TEMP_GROUPED_DATA

Screenshot here:

SQL Server Query Sum By Date And Sum All

1 Answers1