group by help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

group by help

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2006-11-16 : 05:07:17

my sql is
select dategenerated,batch from dvlarep group by batch having dategenerated >=dateserial(2006,11,16) and dategenerated<dateserial(2006,11,16)+1

this generates an error and i know I should do group by batch,dategenerated but if I do will it show by this a list of batches matching the criteria and not repeat a batch twice if it's in for 2 dates?

what can i do?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-16 : 05:12:40

Since you are grouping by batch, you also need a aggregation for dategenerated.
I suggest following

select count(dategenerated),batch from dvlarep where dategenerated >='20061116' and dategenerated<dateadd(day, 1, '20061116') group by batch

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-16 : 05:18:54

Are you using Access? DateSerial is Access function.

Madhivanan

Failing to plan is Planning to fail

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2006-11-16 : 05:24:08

it is access.
peso i tried your suggestion but still get - You tried to execute a query that does not include the specified expression 'dategenerated' as part of an aggregate function

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-16 : 05:28:17

[code]select count(dategenerated),
batch
from dvlarep
where dategenerated >= #2006-11-16#
and dategenerated < #2006-11-17
group by batch[/code]You know there is a special department for ACCESS queries?

Peter Larsson
Helsingborg, Sweden

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2006-11-16 : 05:29:16

but i don't want a count I want to return all the unique batch numbers in my query

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-16 : 05:37:55

You need to post some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2006-11-16 : 05:39:54

i want to see as a result

dategenerated and batch per batch (meaning a batch can be on more then 1 dategenerated but in this query i just want to return the batch number once)
would i be better off using a regular query with distinct batch?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-16 : 05:44:18

Are you trying to suppress column if it is duplicated?

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-16 : 05:45:43

SELECT DISTINCT Batch FROM dvlarep WHERE dategenerated BETWEEN DateSerial(2006, 11, 16) AND DateSerial(2006, 11, 17)

Peter Larsson
Helsingborg, Sweden

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2006-11-16 : 06:18:49

can i select distinct batch but include one of the dategenerated collumns in the results (so I know one date from the batch)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-16 : 06:53:00

quote:
Originally posted by esthera

can i select distinct batch but include one of the dategenerated collumns in the results (so I know one date from the batch)

Do you have any issues in giving sample and expected data?

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-16 : 07:02:07

Esthera, yes you can but YOU mus decide wich date to give.
First, last, any...

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources