CODEWITHSUNDEEP
sqlsqlite
skarface
skarface

Reputation: 910

Group rows by timestamp and conditionally sum different columns

I'm trying to select some traffic flow data and create sums for the different traffic directions, bucketed in 60 second buckets. Simplified table is below (timestamp is unix epoch):

Timestamp      Source     Destination    Count
1              inside     outside          5
2              inside     outside          6
3              outside    inside           7
65             inside     inside           4
66             inside     outside          6
72             inside     outside          7

Current Query (which does the bucketing, but doesn't have anything about direction)

SELECT sum(count) AS total FROM table GROUP BY round(timestamp/60)

This gives me the total count for each 60 second "bucket" like

Count
 18
 10
 7

Now, where I'm lost (I can do this in the client code, but I'd kinda like to do it with the SQL if possible). I'd like to have a sum called inbound for every row with inside as source and dest as outside, a sum called outbound for every row with outside in source and inside as dest, and a row called internal where both source and dest are inside. Still grouped by the 60 second bucket. i.e. what I want back is:

Inbound  Outbound  Internal
   7        11        0
   0         6        4
   0         7        0

My SQL foo is weak, and I really don't know where to start (I suspect I need a subselect, but I'm not sure how to construct it).

Upvotes: 1

Views: 848

Answers (2)

MatBailie
MatBailie

Reputation: 86706

SELECT
  round(timestamp/60)             AS bucket,
  SUM(
    CASE WHEN source = 'inside'  AND destination = 'outside'
         THEN count ELSE 0
    END
  )                               AS outbound,
  SUM(
    CASE WHEN source = 'outside' AND destination = 'inside'
         THEN count ELSE 0
    END
  )                               AS inbound,
  SUM(
    CASE WHEN source = 'inside'  AND destination = 'inside'
         THEN count ELSE 0
    END
  )                               AS internal
FROM
  yourTable
GROUP BY
  round(timestamp/60)

Upvotes: 2

Sean Vieira
Sean Vieira

Reputation: 159885

You should actually be able to do a pivot:

SELECT SUM(count) AS total
    , SUM(CASE 
              WHEN Source = 'outside' 
                   AND Destination = 'inside' 
                   THEN count  -- Use 1 if you only want to count each row
              ELSE 0 END) AS [Inbound]
    , SUM(CASE
              WHEN Source = 'inside' 
                  AND Destination = 'outside' 
                  THEN count
              ELSE 0 END) AS [Outbound]
    , SUM(CASE 
              WHEN Source = 'inside'
                  AND Destination = 'inside' 
                  THEN count
              ELSE 0 END) AS [Internal]
FROM table 
GROUP BY round(timestamp/60)

Upvotes: 1

Related Questions