Duplicates in Group by... - 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 2005 Forums

Transact-SQL (2005)

Duplicates in Group by...

Author

Topic

banavalikar
Starting Member

3 Posts

Posted - 2008-05-22 : 03:44:30

I am quite upto the limit now. My query below shows duplicates (I mean the drivers) evenif I group them -

select ltrim(rtrim(supplier.name)) Company, c.fore, c.name, count(c.linkid) [Total Responses],
CASE WHEN round(AVG(CONVERT(float(3), csr.driver_comm)), 1) > 10 THEN 10 ELSE round(AVG(CONVERT(float(3), csr.driver_comm)), 1)
END AS [Driver Communication]

from supplier inner join contact c on supplier.id = c.linkid
inner join csr on csr.tourcode = supplier.code
inner join booking on csr.bin = booking.id

where supplier.tid = 301
group by supplier.name, csr.bin, c.name, c.fore
order by 1

Output:

A L S Travel.....David Draper.....4.....7
A L S Travel.....Andrew Scott.....16.....10
A L S Travel.....Keath K.....8.....10
A L S Travel.....David Draper.....8.....8.5
A L S Travel.....Keath K.....4.....10
A L S Travel.....Paul Hammond.....4.....7
A L S Travel.....Yan Faczynski.....8.....7
A L S Travel.....Yan Faczynski.....4.....10
A L S Travel.....John Gay.....8.....10
A L S Travel.....Yan Faczynski.....12.....3

Please save me... Someone...

TIA,
Krishna

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-22 : 03:49:37

It seems like you have different bin values for these records? if you want your result as 1 record per driver try removing csr.bin from group by.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources