CODEWITHSUNDEEP
sqlsql-servert-sql
123onetwothree
123onetwothree

Reputation: 686

SELECT SUM () by DISTINCT intervals (Date)

Suppose we have a table:

╔═════════════════════════════════════╗
║ Name   Date                   Value ║
╠═════════════════════════════════════╣
║ John   2013-01-01 10:20:00    10    ║
║ John   2013-01-01 12:20:11    20    ║
║ Mark   2013-01-01 11:44:10    10    ║
║ Mark   2013-01-02 12:00:00    20    ║
║ Mark   2013-01-03 15:20:00    20    ║
║ Tim    2013-01-01 15:11:12    5     ║
║ Tim    2013-01-03 18:44:44    10    ║
║ Tim    2013-01-03 20:11:00    15    ║
╚═════════════════════════════════════╝

And using a single SELECT query, output:

╔════════════════════════════════════════════════╗
║ Name    2013-01-01    2013-01-02    2013-01-03 ║
╠════════════════════════════════════════════════╣
║ John    30            0             0          ║
║ Mark    10            20            20         ║
║ Tim     5             0             25         ║
╚════════════════════════════════════════════════╝

How do you do this in a single SELECT? I tried with SUM(DISTINCT) but no success. I cannot figure out the logic.

It must be GROUP BY Name only (I think), but how would I compute the SUM() by intervals?

Upvotes: 1

Views: 1580

Answers (4)

rplusm
rplusm

Reputation: 33

Try this:

DECLARE @cols AS NVARCHAR(MAX)
       ,@query AS NVARCHAR(MAX)

SELECT @cols = STUFF((
                     SELECT ',' + QUOTENAME(CAST(DATE as DATE))
                     FROM (
                           SELECT DISTINCT CAST(DATE as DATE) DATE FROM #YourTable
                           ) tbl
                     ORDER BY DATE
                     FOR XML PATH('')
                           ,TYPE
                     ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

SET @query = 
       'WITH CTE (
    NAME
    ,DATE
    ,VALUE
    )
AS (
    SELECT NAME
        ,CAST(DATE AS DATE)
        ,Cast(Value AS INT)
    FROM #YourTable
    )
SELECT NAME
    ,' + @cols + '
FROM CTE
PIVOT(SUM(Value) FOR DATE IN (' + @cols + ')) PVT;'

EXECUTE (@query);

Upvotes: 0

ermagana
ermagana

Reputation: 1230

SELECT NAME, 
    ISNULL([2013-01-01],0) [2013-01-01], 
    ISNULL([2013-01-02],0) [2013-01-02], 
    ISNULL([2013-01-03],0) [2013-01-03]
FROM (
    SELECT NAME, CONVERT(DATE, [Date]) [Date], SUM([Value]) [Value]
    FROM   TABLE1
    GROUP  BY NAME, CONVERT(DATE, [Date])
  ) src
PIVOT (
   SUM([Value])
   FOR [Date]
   IN ( [2013-01-01], [2013-01-02], [2013-01-03])
  ) pvt

The date columns may have to be specified differently, depending on how they would print out.

SQL Authority Pivot Example
My SQL Fiddle Example

Upvotes: 2

Lamak
Lamak

Reputation: 70638

If the dates are fixed:

SELECT  [Name],
        SUM(CASE WHEN [Date] >= '20130101' 
            AND [Date] < '20130102' THEN Value END) [2013-01-01],
        SUM(CASE WHEN [Date] >= '20130102' 
            AND [Date] < '20130103' THEN Value END) [2013-01-02],
        SUM(CASE WHEN [Date] >= '20130103' 
            AND [Date] < '20130104' THEN Value END) [2013-01-03]
FROM YourTable
GROUP BY [Name]

Upvotes: 3

Gidil
Gidil

Reputation: 4137

Try this:

SELECT NAME, 
       SUM(CASE 
             WHEN CAST(DATE AS DATE) = '2013-01-01' THEN VALUE 
             ELSE 0 
           END) [2013-01-01], 
       SUM(CASE 
             WHEN CAST(DATE AS DATE) = '2013-01-02' THEN VALUE 
             ELSE 0 
           END) [2013-01-02], 
       SUM(CASE 
             WHEN CAST(DATE AS DATE) = '2013-01-03' THEN VALUE 
             ELSE 0 
           END) [2013-01-03] 
FROM   TABLE1 
GROUP  BY NAME

Take a look at the working example on SQL Fiddle.

Upvotes: 3

Related Questions