Distinct and count - 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)

Distinct and count

Author

Topic

shiyam198
Yak Posting Veteran

94 Posts

Posted - 2010-05-26 : 11:32:32

Hi Guys,

I have the following script.

select order_number,section_name, seatloc_row
from ts_order
inner join ts_order_admission on orderadmission_order_id = order_id
inner join ts_admission on admission_id = ts_order_admission.orderadmission_admission_id
inner join ts_seat_location on seat_location_id = admission_seat_location_id
inner join ts_section on section_id = seatloc_section_id
where distinct(seatloc_row) > 2

My intension is to find the orders where a distinct(row) > 2

just to illustrate my point,

If i do -

select order_number,section_name, seatloc_row
from ts_order
inner join ts_order_admission on orderadmission_order_id = order_id
inner join ts_admission on admission_id = ts_order_admission.orderadmission_admission_id
inner join ts_seat_location on seat_location_id = admission_seat_location_id
inner join ts_section on section_id = seatloc_section_id
where order_number = 3

It will display the following of 36 rows.

http://img214.imageshack.us/img214/1674/resultf.png

Reason is the order number 3 has 36 seats in the same row J.

But i need to capture the orders, if they have more than one unique row in them.

When i run the first script, I get the error.

Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'distinct'.

Any help is much appreciated.

- Shiyam

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-26 : 11:35:14

select order_number,section_name, seatloc_row
from ts_order
inner join ts_order_admission on orderadmission_order_id = order_id
inner join ts_admission on admission_id = ts_order_admission.orderadmission_admission_id
inner join ts_seat_location on seat_location_id = admission_seat_location_id
inner join ts_section on section_id in (seatloc_section_id
where order_number = 3)

Madhivanan

Failing to plan is Planning to fail

shiyam198
Yak Posting Veteran

94 Posts

Posted - 2010-05-26 : 11:42:27

Thanks Madhivannan, But it still returns 36 rows. I want it to return one row because it has only one unique row - J.

is there a way to do it?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-26 : 11:46:41

What is the column name?

Madhivanan

Failing to plan is Planning to fail

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-26 : 11:46:57

add

group by order_number,section_name, seatloc_row

to your query.

shiyam198
Yak Posting Veteran

94 Posts

Posted - 2010-05-26 : 12:04:18

Hey,

The column name is seatloc_row. I want to find number of unique rows in the order.

The following returns 36, because the order has 36 rows of seats.
e.g. section01, J, 1
section01, J, 2
section01, J, 3
e.t.c

I want it to return 1 row and say, there is one unique row (seatloc_row) in the order which is J.

Is that possible?

select order_number,section_name, count(seatloc_row)
from ts_order
inner join ts_order_admission on orderadmission_order_id = order_id
inner join ts_admission on admission_id = ts_order_admission.orderadmission_admission_id
inner join ts_seat_location on seat_location_id = admission_seat_location_id
inner join ts_section on section_id = seatloc_section_id
Group by order_number,section_name

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-27 : 02:56:37

select order_number,section_name, max(seatloc_row)
from ts_order
inner join ts_order_admission on orderadmission_order_id = order_id
inner join ts_admission on admission_id = ts_order_admission.orderadmission_admission_id
inner join ts_seat_location on seat_location_id = admission_seat_location_id
inner join ts_section on section_id = seatloc_section_id
Group by order_number,section_name

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources