SELECT - 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)

SELECT

Author

Topic

DENIZ3E
Yak Posting Veteran

56 Posts

Posted - 2005-02-08 : 08:35:56

SELECT DISTINCT(EKIMNO),SIPNO,FIDEADI,BAYI,URETICI,VIOL,TOHH,VIOLADP,TOHC,TOHKAY,TESLIMTARIHI FROM VFUDTOHUMRAP WHERE
(RP='P') OR (RP='R' AND DRM<>'P') AND EKIMNO IN (SELECT EKIMNO FROM VFUDEKIMNOSIRASI)
----------------------
SIPNO IS A COLOMN
IF SIPNO COUNT(*)>1 'MANY'
IF SIPNO COUNT(*)=1 'FEW'
HOW CAN I USE THIS SENTENCES IN THE ABOVE QUERY.
AND I DO GRUP WITH EKIMNO (EKIMNO IS COLOMN)
PLEASE HELP

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-08 : 08:40:58

yes you would group on ekimno:


Select 
	ekimno,
	sipno = case when count(*) = 1 then 'few' else 'many' end,
	fideadi,
	bayi,
	...
From VFUDTOHUMRAP
Where (RP='P') OR (RP='R' AND DRM<>'P') AND EKIMNO IN (SELECT EKIMNO FROM VFUDEKIMNOSIRASI)

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

DENIZ3E
Yak Posting Veteran

56 Posts

Posted - 2005-02-08 : 08:55:39

VERY THANKS
but ERROR!!

Server: Msg 8118, Level 16, State 1, Line 1
Column 'VFUDTOHUMRAP.EKIMNO' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'VFUDTOHUMRAP.FIDEADI' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
......
....
....

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-08 : 09:30:50

well you've got to make it your own...

I did say you should group on ekimno


Select 
	ekimno,
	sipno = case when count(*) = 1 then 'few' else 'many' end
From VFUDTOHUMRAP
Where (RP='P') OR (RP='R' AND DRM<>'P') AND EKIMNO IN (SELECT EKIMNO FROM VFUDEKIMNOSIRASI)
Group By ekimno

If you want to show other columns, then you may need to group on those columns as well

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

DENIZ3E
Yak Posting Veteran

56 Posts

Posted - 2005-02-08 : 16:46:30

yes ý want to see all the columns. ARE THERE ANY OTHER SOLUTIONS.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-08 : 16:51:08

do you have some sample data we could work with??

without sample data I can tell you that if you want other columns you either need to add them to the group by clause.... or you can wrap them in an aggregate function like sum() or max() or min()... etc

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

DENIZ3E
Yak Posting Veteran

56 Posts

Posted - 2005-02-08 : 17:07:21

EKIMNO SIPNO FIDEADI BAYI URETICI VIOL ADET TOH
------ ----- ------- ---- ------- ---- ---- ----
1 1380 ENGINAR ALI MEHMET 128 10 0
1 1381 ENGINAR ALI VELI 128 20 0
1 1382 ENGINAR ALI ALI 128 25 0
2 1383 TOMATO VELI AYSE 210 55 1
2 1386 TOMATO AHMET HUSEYIN 210 60 1
3 1387 KARPUZ ALI ALI 300 50 0
4 1388 BAHARAT VELI VELI 300 55 1

I WANT TO SEE..

EKIMNO SIPNO FIDE ADI BAYI URETICI VIOL ADET TOH
------ ------ ------- ------ -------- ---- ---- ----
1 3 AMOUNT ENGINAR 3 AMOUNT 3A MOUNT 128 SUM(ADET) 0
...
3 1387 KARPUZ ALI ALI 128 300 1

DENIZ3E
Yak Posting Veteran

56 Posts

Posted - 2005-02-08 : 17:27:41

EKIMNO SIPNO FIDEADI BAYI URETICI VIOL ADET TOH
------ ----- ------- ---- ------- ---- ---- ----
1 1380 ENGINAR ALI MEHMET 128 10 0
1 1381 ENGINAR ALI VELI 128 20 0
1 1382 ENGINAR ALI ALI 128 25 0
2 1383 TOMATO VELI AYSE 210 55 1
2 1386 TOMATO AHMET HUSEYIN 210 60 1
3 1387 KARPUZ ALI ALI 300 50 0
4 1388 BAHARAT VELI VELI 300 55 1

I WANT TO SEE..

EKIMNO SIPNO FIDEADI BAYI URETICI VIOL ADET TOH
------ ------ ------- ------ -------- ---- ---- ----
1 3AMOUNT ENGINAR 3AMOUNT 3AMOUNT 128 SUM(ADET) 0
...
3 1387 KARPUZ ALI ALI 128 300 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources