1

I read berfore SQL Count for a Date Column and SQL to get count by date but these does not point my problem.

I have the following table records (the table has only two columns)

| VALUE   | TIME               |
--------------------------------
| test    | 04.05.2012 11:46:46|
| test2   | 04.05.2012 11:46:49|
| test3   | 06.05.2012 11:47:46|
| test2   | 05.05.2012 11:47:46|
| test    | 04.05.2012 11:51:46|

I want to display like:

| VALUE   | COUNT   | NEWTIME  |
--------------------------------
| test    | 2       |04.05.2012|
| test2   | 1       |04.05.2012|
| test2   | 1       |05.05.2012|
| test3   | 1       |06.05.2012|

I counted VALUE by using day as datepart of TIME like:

select datepart(d, TIME), count(VALUE) from table1
group by datepart(d, TIME)

How to get also the additional NEWTIME column ? It is possible in SQL Server 2005 ?

Thank you

Community
  • 1
  • 1
Snake Eyes
  • 16,287
  • 34
  • 113
  • 221

5 Answers5

2
select VALUE,
       count(*) as COUNT,
       dateadd(day, datediff(day, 0, TIME), 0) as NEWTIME
from YourTable
group by VALUE, dateadd(day, datediff(day, 0, TIME), 0)

SE-Data

Community
  • 1
  • 1
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
  • how to group by year+month+day ? – Snake Eyes May 07 '12 at 12:33
  • Can show me please how to leave hours, minutes and seconds intact ? because if I use `dateadd(day, datediff(day, 0, TIME), 0)` then the time is 00:00:00. I want to leave them intact – Snake Eyes May 08 '12 at 06:12
  • @MichaelSwan - You can't have the time part in the result and at the same time group on date only. – Mikael Eriksson May 08 '12 at 06:34
  • Then per hour if is possible or per minutes – Snake Eyes May 08 '12 at 08:55
  • @MichaelSwan You can have the time part for **one** of the rows that makes up a group. Like `min(TIME)` or `max(TIME)`. If you think about it you see that if a group on day is composed of many rows with different time parts you have to choose what time you want on the row. – Mikael Eriksson May 08 '12 at 09:07
1

You can add it and format it like so, but you will have to also group by it.

select value, datepart(d, TIME), count(VALUE), datepart(mmonth, time) + '.' + datepart(day, time) + '.' + datepart(year, time) as newtime
from table1
group by datepart(d, TIME, newtime)

If you don't care about using the period as your seperator, then it can be done a bit easier.

select value, datepart(d, TIME), count(VALUE), convert(varchar, time, 101) as newtime
from table1
group by datepart(d, TIME, newtime)

I also noticed that you have duplicates in your output, personally, I would do this instead:

select value, count(*) as cnt, convert(varchar, time, 101) as newtime
from table1
group by value, newtime
Chris Gessler
  • 22,727
  • 7
  • 57
  • 83
0
select VALUE, count(VALUE), datepart(d, MIN(TIME)) AS NEWTIME
from table1
group by datepart(d, TIME)

You can use either MIN or MAX aggregate function. In your case it shouldn't matter as you're grouping at a whole day level via datepart.

Joseph Victor Zammit
  • 14,760
  • 10
  • 76
  • 102
0

havent tested, but i would do it like that

select VALUE, count(*) AS COUNT, convert(date, max(TIME)) AS NEWTIME --max , min whatever 
from table1
group by VALUE, datepart(d, TIME) --dont know why you group by timne not value
WKordos
  • 2,167
  • 1
  • 16
  • 15
0

I dont really get your question but if I understand it correcty you would like to group the data by short date from the time column? If so the result is:

select max(VALUE) as VALUE, count(VALUE) as count, CONVERT(VARCHAR(10), TIME , 104) as newtime
from table1
group by CONVERT(VARCHAR(10), TIME , 104)

I dont get why to display the values if you don't group by them

Jester
  • 3,069
  • 5
  • 30
  • 44