CODEWITHSUNDEEP
sqlsql-server-2008t-sql
fdkgfosfskjdlsjdlkfsf
fdkgfosfskjdlsjdlkfsf

Reputation: 3301

Without using DISTINCT, how to group data without altering value?

I have a feeling this is a dumb question with a simple answer, but here goes.

How can I group the following data without using DISTINCT? @Table has 5 rows, which shows data for Hrs 5-9. I just don't like DISTINCT.

Since I need to display all hours of the day upto Hr9 (including 0-4), I'm joining it with table DimTime. DimTime has all hours, but with its 15-min intervals. So, DimTime looks like this:

Hour    Minute
0       0
0       15
0       30
0       45
1       0
1       15
1       30
1       45

So here's my script:

declare @table table
(
    Hour int,
    Value int
)

insert into @table select 5, 25
insert into @table select 6, 34
insert into @table select 7, 54
insert into @table select 8, 65
insert into @table select 9, 11

select d.hour, t.hour, sum(value) 
from @table t 
left join dimtime d on d.hour = t.hour
group by d.hour, t.hour

If I use GROUP BY, then I need to have an aggregate function. So if I use SUM, it'll multiply all values by 4. If I remove the aggregate function, I'll get a syntax error.

Also, I cannot use a CTE since the contents in @table comes from a CTE (I just didn't include it here).

Here's the result that I need to display:

Hour    Value
0       null
1       null
2       null
3       null
4       null
5       25
6       34
7       54
8       65
9       11

Upvotes: 1

Views: 65

Answers (5)

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17177

If you really with to skip the sorting operation on dimtime with the use of distinct clause then check the below explanation.

Display all hours (0-9) from dimtime and sum the value given in @table for a particular hour:

SELECT
  d.hour, SUM(t.value)
FROM
  dimtime d
  LEFT JOIN @table t
    ON d.hour = t.hour
WHERE d.minute = 0 -- retrieves one row for every hour from dimtime
GROUP BY d.hour
ORDER BY d.hour -- not needed, but will give you resultset sorted by hour

Assuming that you have a row with value minute = 0 in your dimtable for every hour you could just limit the rows retrieved for join operation. That will work with any value from list 0, 15, 30, 45.

SUM() will work properly by summing all the values for a given hour in @table. If there are no rows with a particular hour, it will return 0 value.

Upvotes: 2

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

Try

SELECT *
FROM DimTime D
LEFT JOIN myTable T
       ON D.Hour = T.Hour
WHERE D.Minute = 0

SQL Fiddle Demo

Output

| Hour | Minute |   Hour |  Value |
|------|--------|--------|--------|
|    0 |      0 | (null) | (null) |
|    1 |      0 | (null) | (null) |
|    2 |      0 | (null) | (null) |
|    3 |      0 | (null) | (null) |
|    4 |      0 | (null) | (null) |
|    5 |      0 |      5 |     25 |
|    6 |      0 |      6 |     34 |
|    7 |      0 |      7 |     54 |
|    8 |      0 |      8 |     65 |
|    9 |      0 |      9 |     11 |

Upvotes: 0

Tom H
Tom H

Reputation: 47392

  1. You should have a better reason for not using a programming function than "I just don't like it"
  2. You can have a CTE that uses another CTE

@dnoeth provided an excellent answer, but here's another option:

SELECT
    d.hour,
    t.value
FROM
    @table t
INNER JOIN (SELECT DISTINCT hour FROM dimTime) d ON d.hour = t.hour

Upvotes: 0

dnoeth
dnoeth

Reputation: 60482

Simply add a condition WHERE minute = 0 to return only one row per hour.

Upvotes: 2

D Stanley
D Stanley

Reputation: 152626

If I use GROUP BY, then I need to have an aggregate function

Only if you include expressions in your SELECT that are not part of your group key. You could certainly do

select d.hour, t.hour, value 
from @table t
inner join dimtime d
  on d.hour = t.hour
group by d.hour, t.hour, value

or

select d.hour, t.hour, MIN(value) 
from @table t
inner join dimtime d
  on d.hour = t.hour
group by d.hour, t.hour

Note that the first query gives you the exact same results as DISTINCT (and may even be compiled to the same query plan) so I'm not sure what your aversion is to DISTINCT.

Upvotes: -1

Related Questions