how to get around problems with group by, aggregate

Question

I have the following query:

SELECT d.currentDate, t.ConfigId, t.value as value
FROM    #Dates AS d
        OUTER APPLY
        (   SELECT  t.value as value, t.FK_ShipDataSubsystemConfigId
            FROM    myTable AS t
            WHERE   t.[Timestamp] <= d.currentDate and t.ConfigId in (' + @IDList + ')
            and t.FK_DataSystemId <> 1
        ) AS t 
GROUP BY d.currentDate, t.ConfigId
)

where Dates is just a table holding a bunch of datetimes I am using to ensure I get the data at the intervals I need.

My problem is with the group by clause. The query doesn't work as is because value is not in the group by or an aggregate function. I tried grouping by value also to make the error go away, but it just gives me every single date in the interval I am selecting on matched to every single value -- not what I want. I should end up with a table with one row for each date/ConfigId pairing.

When I remove value from the select and just get the date and the ConfigId, it works fine. I get the exact number of rows I should be getting.

The table I'm pulling from looks like this:

PK_DataId    Timestamp        value     ConfigId
1            1/1/2015 12:00   234       5
2            1/1/2015 12:01   456       4

I am expecting to get back this:

Timestamp            value        ConfigId
1/1/2015 12:00:00    234          5
1/1/2015 12:00:00    456          4

Where I have a value for each configId/date pair from every fifteen minutes. When I add max(value) I only get one value for every time instead of different ones. When I group by value I get millions of rows, it looks like I am getting one row for each timestamp matched with each value from any other timestamp. I don't really understand what is happening.

How can I get those results while also selecting value?

Try including an `aggregate` function on the `value` column. — Vamsi Prabhala, Jul 16 '15 at 18:39
Which value do you want? Or are you looking for some kind of comma separated list? — James Z, Jul 16 '15 at 18:42
You say you don't want every value of `value`, but you don't say which value of `value` you *do* want. So which is it? The highest? Lowest? Average? — Bacon Bits, Jul 16 '15 at 18:42
Amen to the comment by @BaconBits. And some sample data would be nice. — , Jul 16 '15 at 18:45
google and learn about GROUP BY and Aggregate Functions in SQL Server. — Tab Alleman, Jul 16 '15 at 18:49

Gordon Linoff · Accepted Answer · 2015-07-17T03:04:57.207

If I understand correctly, you just want top 1 in the apply:

SELECT d.currentDate, t.ConfigId, t.value as value
FROM #Dates d OUTER APPLY
     (SELECT  TOP 1 t.value as value, t.FK_ShipDataSubsystemConfigId
      FROM    myTable AS t
      WHERE   t.[Timestamp] <= d.currentDate and
              t.ConfigId in (' + @IDList + ') and
              t.FK_DataSystemId <> 1
     ORDER BY t.[Timestamp] DESC
    ) t ;

EDIT:

If you need one row for each config and time:

SELECT d.currentDate, t.ConfigId, t.value as value
FROM #Dates d CROSS JOIN
     (SELECT DISTINCT FK_ShipDataSubsystemConfigId
      FROM myTable
      WHERE t.ConfigId in (' + @IDList + ')
     ) c OUTER APPLY
     (SELECT  TOP 1 t.value as value, t.FK_ShipDataSubsystemConfigId
      FROM    myTable AS t
      WHERE   t.[Timestamp] <= d.currentDate and
              t.ConfigId = c.FK_ShipDataSubsystemConfigId and
              t.FK_DataSystemId <> 1
      ORDER BY t.[Timestamp] DESC
     ) t ;

Same basic idea, but this needs to generate all the rows for all configs and the dates before the outer apply.

It's closer, I am getting different values now. But I am only getting one row for each time, when I should have one row for each time/configId. — eddie_cat, Jul 16 '15 at 19:16

score 0 · Answer 2 · edited May 23 '17 at 11:43

0

Without real data is really hard to understand your question.

Im going to guess you dont want Aggregeted function like.

SELECT d.currentDate, t.ConfigId, 
  MAX(t.value) as maxvalue, 
  MIN(t.value) as minvalue,
  COUNT(t.value) as totalvalue

So im going with you are looking for

simulating-group-concat-mysql-function-in-sql-server

ANDY   |  A100
ANDY   |  B391
ANDY   |  X010
TOM    |  A100
TOM    |  A510

returns:

ANDY   |  A100 / B391 / X010
TOM    |  A100 / A510

edited May 23 '17 at 11:43

Community

1
1

answered Jul 16 '15 at 18:53

Juan Carlos Oropeza

47,252
12
78
118

I added some sample data if that helps – eddie_cat Jul 16 '15 at 18:55
Looks like you dont need group by. Because only item for each date. Either remove the `GROUP BY` or put a bigger sample. – Juan Carlos Oropeza Jul 16 '15 at 19:00

how to get around problems with group by, aggregate

2 Answers2