Home Forums SQL Server 2008 SQL Server Newbies Coding Counts and Subtotals by day, location and grand totals in T-SQL RE: Coding Counts and Subtotals by day, location and grand totals in T-SQL

  • Does this get you closer?

    SELECT CONVERT( CHAR(10), sosb_CarDetail_DateReleased, 101 ) AS [DateReleased]

    , Count( sosb_CarDetail_CarNumber ) AS TotalCarsByDate

    , sosb_SP_Description

    , Round( Sum( sosb_CarDetail_NetTons ), 2 ) AS Total_NetTons

    FROM #mytable

    GROUP BY CONVERT( CHAR(10), sosb_CarDetail_DateReleased, 101 )

    , sosb_SP_Description

    ORDER BY CONVERT( CHAR(10), sosb_CarDetail_DateReleased, 101 ) DESC

    SELECT Datepart( Month, a.DateReleased ) AS MonthReleased

    , Sum( a.TotalCarsByDate ) AS GrandTotalCars

    , sosb_SP_Description

    , Sum( Total_NetTons ) AS TotalNetTonsByMonth

    FROM ( SELECT CONVERT( CHAR(10), sosb_CarDetail_DateReleased, 101 ) AS [DateReleased]

    , Count( sosb_CarDetail_CarNumber ) AS TotalCarsByDate

    , sosb_SP_Description

    , Round( Sum( sosb_CarDetail_NetTons ), 2 ) AS Total_NetTons

    FROM #mytable

    GROUP BY CONVERT( CHAR(10), sosb_CarDetail_DateReleased, 101 )

    , sosb_SP_Description ) a

    GROUP BY Datepart( Month, a.DateReleased )

    , sosb_SP_Description