CODEWITHSUNDEEP
sqlsql-server
steve8918
steve8918

Reputation: 1860

efficient way to group second data on a per-day basis?

I have a table of data for a particular stock that contains every single transaction going back a few years. I want to group this data on a per-day basis, so that I can see things like the daily volume, etc.

The best way I have come up with so far is:

select 
datepart(year, date),
datepart(month, date),
datepart(day, date), 
sum(volume) from hi 
group by 
datepart(year, date),
datepart(month, date),
datepart(day, date)
order by
datepart(year, date),
datepart(month, date),
datepart(day, date)

My SQL is somewhat limited, so I'm wondering if there's a more efficient way to do this?

Upvotes: 1

Views: 259

Answers (4)

Amy B
Amy B

Reputation: 110171

SELECT DateAdd(dd, sub.DayCount, 0) as TheDate, SUM(sub.Volume)
FROM
(SELECT DateDiff(dd, 0, date) as DayCount, Volume FROM hi)
as Sub
GROUP BY Sub.DayCount
ORDER BY Sub.DayCount

As far as efficiency goes, both queries must read the whole table once. They incur the same IO. You should messure IO with SET STATISTICS IO ON to see if that's a problem.

Since it seems that IO is the real issue, one option is to create an index which includes both Volume, DayCount (and no other columns).

Upvotes: 1

Jake Feasel
Jake Feasel

Reputation: 16955

Here's another method:

http://www.sqlfiddle.com/#!3/25480/4

select
  sum(volume) as totalPerDay,
  simple_date
from
(
select 
  convert(varchar, [date], 101) as simple_date, 
  volume, 
  id
from hi
) tmp
group by
  simple_date

Full disclosure: sqlfiddle.com is my site.

Upvotes: 0

zzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
zzzzzzzzzzzzzzzzzzzzzzzzzzzzzz

Reputation: 5759

Here is what you are looking for

SELECT CONVERT(VARCHAR, hi.date,101), SUM(volume) 
FROM hi 
GROUP BY CONVERT(VARCHAR, hi.date,101)

Upvotes: 0

Andomar
Andomar

Reputation: 238206

In SQL Server 2008 and above, you can use the date type. It contains only the date part of a datetime:

group by cast([date] as date)

Or for earlier versions:

group by convert(varchar(10), [date],121)

This groups on the first 10 characters of the ODBC date, or yyyy-mm-dd.

Upvotes: 1

Related Questions