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)

group by

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-01-10 : 06:27:20

select lastname,picture, count(lastname) from images where date>='2007-12-11' and date<='2007-12-11 23:59' group by plate

how can i get the pictures included but still group by plate

i basically want to be able to query all plates that have only 1 lastname in the criteria and not more then that.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-10 : 06:30:40

Try this

Select t1.* from images t1 inner join
(select plate,count(lastname) as counting from images group by plate having count(lastname)=1) as t2
on t1.plate=t2.plate

Madhivanan

Failing to plan is Planning to fail

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-01-10 : 06:38:11

but i need the where criteria in both ends

there's no way to do a groupby but include another field

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-10 : 06:46:41

what is pk of table?

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-01-10 : 06:51:58

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-10 : 06:54:24

Try this:-

select t1.lastname,t1.picture,t2.lastnamecount from images t1
inner join (select plate,count(lastname) as 'lastnamecount' from images
where date>='2007-12-11' and date<='2007-12-11 23:59' group by plate)t2
on t2.plate=t1.plate
where t1.date>='2007-12-11' and t1.date<='2007-12-11 23:59'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-10 : 07:26:48

quote:
Originally posted by esthera

but i need the where criteria in both ends

there's no way to do a groupby but include another field

Well. If that is the case, dont you really know how to do that?

Madhivanan

Failing to plan is Planning to fail

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-01-10 : 08:44:59

esthera -- be sure to read this:

http://weblogs.sqlteam.com/jeffs/archive/2007/07/20/60261.aspx

If you want to include other columns in a grouped SELECT, you must logically specify exactly how you want those values to be returned. Sample data, and explanation of your logic, and expected results greatly helps us to help you as well. Which , I suspect you already know since you have 600 posts here!

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources