When a group show date field - 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 2008 Forums

Transact-SQL (2008)

When a group show date field

Author

Topic

webcakali
Starting Member

16 Posts

Posted - 2011-02-08 : 15:40:01

Select KacKisi=COUNT(KullAdi),KullAdi,AdSoyad,AdSoyadGizli,Tarih=Tarih From
(Select
KullAdi=fldKullAdi,
AdSoyad=fldAdSoyad,
AdSoyadGizli=fldAdSoyadGizli,
Tarih=fldKayit
From tblFotoLog
Where fldFoto = 84920 And Not fldUye = 0) As A
Group By KullAdi,AdSoyad,AdSoyadGizli

Get Error
Msg 8120, Level 16, State 1, Line 12
Column 'A.Tarih' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Select KacKisi=COUNT(KullAdi),KullAdi,AdSoyad,AdSoyadGizli,Tarih=Tarih From
(Select
KullAdi=fldKullAdi,
AdSoyad=fldAdSoyad,
AdSoyadGizli=fldAdSoyadGizli,
Tarih=fldKayit
From tblFotoLog
Where fldFoto = 84920 And Not fldUye = 0) As A
Group By KullAdi,AdSoyad,AdSoyadGizli,Tarih

According to the group does not work that way, but used me

could you help? thanks.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-08 : 16:30:23

Can you be more clear on what you want in the output?

It looks like what you want is simply the subquery..

Select
KacKisi=COUNT(*),
KullAdi=fldKullAdi,
AdSoyad=fldAdSoyad,
AdSoyadGizli=fldAdSoyadGizli,
Tarih=fldKayit
FROM tblFotoLog
Where fldFoto = 84920 And Not fldUye = 0
Group By fldKullAdi,fldAdSoyad,fldAdSoyadGizli,fldKayit

Poor planning on your part does not constitute an emergency on my part.

webcakali
Starting Member

16 Posts

Posted - 2011-02-08 : 18:22:34

fldKayit should not do according to the group. I want to make the output should give the field fldKayit "dataguru"

fldKayit Format: 02/07/2011 10:27:49.000

According to the group should do just used me.

I'm sorry I'm turning from google translate

data is as follows

fldID fldKullAdi fldAdSoyad fldAdSoyadGizli fldTarih
1 Mehmet Mehmet SALKIM 0 2011.02.07 10.27.49.010
2 Sahin Sahin SALKIM 0 2011.01.05 11.37.49.010
3 Mehmet Mehmet SALKIM 0 2010.01.05 09.20.49.010
4 Kübra Kübra Salkim 0 2011.01.05 08.22.49.010
5 Suat Suat Salkim 0 2010.02.07 10.28.49.010
6 Özay Özay Salkim 0 2009.01.03 13.13.49.010
7 Özay Özay SALKIM 0 2011.01.07 16.12.49.010
8 Kübra Kübra SALKIM 0 2011.03.09 19.11.49.010
9 Mehmet Mehmet SALKIM 0 2008.05.07 09.20.49.010

I want to be this way

KacKisi KullAdi AdSoyad AdSoyadGizli Tarih
3 Mehmet Mehmet SALKIM 0 2011.02.07
1 Sahin Sahin SALKIM 0 2011.01.05
2 Kübra Kübra SALKIM 0 2011.03.09
1 Suat Suat SALKIM 0 2010.02.07
2 Özay Özay SALKIM 0 2011.01.07

I would like to have the dates DESC

thankss

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-08 : 18:35:03

[code]
DECLARE @Foo table (
fldID int identity(1,1)
,fldKullAdi varchar(20) not null
,fldAdSoyad varchar(10) not null
,fldAdSoyadGizli int not null
,fldTarih datetime
)
INSERT INTO @foo
SELECT 'Mehmet Mehmet','SALKIM', 0, '20110207 10:27:49.010' UNION
SELECT 'Sahin Sahin','SALKIM', 0, '20110105 11:37:49.010' UNION
SELECT 'Mehmet Mehmet','SALKIM', 0, '20100105 09:20:49.010' UNION
SELECT 'Kübra Kübra','SALKIM', 0, '20110105 08:22:49.010' UNION
SELECT 'Suat Suat','SALKIM', 0, '20100207 10:28:49.010' UNION
SELECT 'Özay Özay','SALKIM', 0, '20090103 13:13:49.010' UNION
SELECT 'Özay Özay','SALKIM', 0, '20110107 16:12:49.010' UNION
SELECT 'Kübra Kübra','SALKIM', 0, '20110309 19:11:49.010' UNION
SELECT 'Mehmet Mehmet','SALKIM', 0, '20080507 09:20:49.010'

SELECT KacKisi = COUNT(*)
,KullAdi =fldKullAdi
,AdSoyad = fldAdSoyad
,AdSoyadGizli = fldAdSoyadGizli
,Tarih = MAX(fldTarih)
FROM @Foo
GROUP BY fldKullAdi
,fldAdSoyad
,fldAdSoyadGizli
ORDER BY MAX(fldTarih) DESC
[/code]

[code]
KacKisi KullAdi AdSoyad AdSoyadGizli Tarih
2 Kübra Kübra SALKIM 0 2011-03-09 19:11:49.010
3 Mehmet Mehmet SALKIM 0 2011-02-07 10:27:49.010
2 Özay Özay SALKIM 0 2011-01-07 16:12:49.010
1 Sahin Sahin SALKIM 0 2011-01-05 11:37:49.010
1 Suat Suat SALKIM 0 2010-02-07 10:28:49.010

[/code]

Does that help?

Poor planning on your part does not constitute an emergency on my part.

webcakali
Starting Member

16 Posts

Posted - 2011-02-08 : 18:40:23

You're one great

Thank you very much.

exactly the way I want.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-08 : 18:41:44

You are welcome.

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources