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

Subquery

Author

Topic

dimoss
Yak Posting Veteran

52 Posts

Posted - 2006-05-28 : 07:39:54

Hi,

I have a table with 4 fields (id, nomarxia, adt, moria)
I want to make a query which will look for a specific "adt" (ex WHERE adt="something") and will return the "nomraxia" and "moria".
Till now it's apiece of cake.

However I want with these results ("nomarxia") to receive the MAX(moria), MIN(moria) and COUNT(id). I know that it has to be a subquery...but how?

Please help..

Denis

EXAMPLE with fields

Table: DE

id nomarxia adt moria
1...Ath......M1234....700
2...Ath.......F5323....800
3...Ait.......M1234....700
4...Kav.......M1234....700
5...Xan.......F5323....800
6...Kav.......F5323....800
7...Spa.......G5654....1000
8...Xan.......L9876....1100
9...Ait.......P2134....950
10..Kav.......M1234....600

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-28 : 07:48:07

is this what you want ?

select nomarxia, max(moria), min(moria), count(id)
from   table
group by nomarxia

dimoss
Yak Posting Veteran

52 Posts

Posted - 2006-05-28 : 07:51:06

quote:
Originally posted by khtan

is this what you want ?
select nomarxia, max(moria), min(moria), count(id)
from table
group by nomarxia

KH

Nope...I have already did that

I want in these results to find MAX, MIN and COUNT...
I mean the results according to WHERE Clause..

See the table above in the 1st post

example:
I want the max(moria), min(moria), count(id) of all where adt = "M1234"

www.tabletennis.gr

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-28 : 08:11:56

I see you have posted some sample data. Can you post the expected result ?

dimoss
Yak Posting Veteran

52 Posts

Posted - 2006-05-28 : 08:16:54

quote:
Originally posted by khtan

I see you have posted some sample data. Can you post the expected result ?

KH

For the selected condition and I mean where adt = "M1234"
I expect to get this

nomarxia.moria.MAX_moria..MIN_moria..Count_id
Ath......700...800........700........2
Ait......700...950........700........2
Kav......700...800........600........3

www.tabletennis.gr

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-28 : 08:23:45

Based on the sample data, i got slighly different result. Maybe i miss interpret your requirement on min() & max().
Anyway have a look at this.

declare @DE table
(
	id 		int,
	nomarxia 	varchar(10),
	adt 		varchar(10),
	moria		int
)

insert into @DE
select	1, 'Ath', 'M1234', 700	union all
select	2, 'Ath', 'F5323', 800	union all
select	3, 'Ait', 'M1234', 700	union all
select	4, 'Kav', 'M1234', 700	union all
select	5, 'Xan', 'F5323', 800	union all
select	6, 'Kav', 'F5323', 800	union all
select	7, 'Spa', 'G5654', 1000	union all
select	8, 'Xan', 'L9876', 1100	union all
select	9, 'Ait', 'P2134', 950	union all
select	10, 'Spa', 'H9735', 980

select	a.nomarxia, a.moria, b.max_moria, b.min_moria
from
(
	select	nomarxia, moria
	from	@DE
	where	adt = 'M1234'
) a
inner join
(
	select	nomarxia, max(moria) as max_moria, min(moria) as min_moria
	from	@DE
	group by nomarxia
) b
on	a.nomarxia	= b.nomarxia

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-28 : 08:29:58

Based on you modifed samples data & result, i changed accordingly to below.

declare @DE table
(
	id 		int,
	nomarxia 	varchar(10),
	adt 		varchar(10),
	moria		int
)

insert into @DE
select	1, 'Ath', 'M1234', 700	union all
select	2, 'Ath', 'F5323', 800	union all
select	3, 'Ait', 'M1234', 700	union all
select	4, 'Kav', 'M1234', 700	union all
select	5, 'Xan', 'F5323', 800	union all
select	6, 'Kav', 'F5323', 800	union all
select	7, 'Spa', 'G5654', 1000	union all
select	8, 'Xan', 'L9876', 1100	union all
select	9, 'Ait', 'P2134', 950	union all
select	10, 'Kav', 'M1234', 600

select	a.nomarxia, a.moria, b.max_moria, b.min_moria, b.cnt
from
(
	select	nomarxia, max(moria) as moria
	from	@DE
	where	adt = 'M1234'
	group by nomarxia
) a
inner join
(
	select	nomarxia, max(moria) as max_moria, min(moria) as min_moria, count(*) as cnt
	from	@DE
	group by nomarxia
) b
on	a.nomarxia	= b.nomarxia

/* Result : 
nomarxia   moria       max_moria   min_moria   cnt         
---------- ----------- ----------- ----------- ----------- 
Ait        700         950         700         2
Ath        700         800         700         2
Kav        700         800         600         3

(3 row(s) affected)
*/

dimoss
Yak Posting Veteran

52 Posts

Posted - 2006-05-28 : 08:31:42

It doesn't work...:-(

I forgot to mention that I work in Access..the table is in Access..Sorry..:-(

www.tabletennis.gr

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-28 : 08:33:50

quote:
Originally posted by dimoss

It doesn't work...:-(

I forgot to mention that I work in Access..the table is in Access

www.tabletennis.gr

Oh Access

. My query works on MS SQL Server. Anyway when you run the query in access, any error encounter ?

dimoss
Yak Posting Veteran

52 Posts

Posted - 2006-05-28 : 08:44:47

quote:
Originally posted by khtan

quote:
Originally posted by dimoss

It doesn't work...:-(

I forgot to mention that I work in Access..the table is in Access

www.tabletennis.gr

Oh Access . My query works on MS SQL Server. Anyway when you run the query in access, any error encounter ?

KH

Syntax error in query. Incomplete query clause.

www.tabletennis.gr

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-28 : 09:02:31

Try this.

select	nomarxia, max(moria) as moria,
        (select max(moria) from DE x where x.nomarxia = d.normaxia) as max_moria,
        (select min(moria) from DE x where x.nomarxia = d.normaxia) as min_moria,
        (select count(*)   from DE x where x.nomarxia = d.normaxia) as cnt
from	DE d
where	adt = 'M1234'
group by nomarxia

If it does not work, try posting at http://www.sqlteam.com/forums/forum.asp?FORUM_ID=3.

Sorry i can't help you much as not familiar with MS Access.

dimoss
Yak Posting Veteran

52 Posts

Posted - 2006-05-28 : 09:08:04

Didn't work...
Ask for parameter value "moria"

Anyway...I appreciate your help...:-)

I will try to post it in the forum you mentioned

www.tabletennis.gr

cmdr_skywalker
Posting Yak Master

159 Posts

Posted - 2006-05-29 : 01:14:52

you need to have a table column named "moria". Try to use the query using the actual table and column. if you can't figure that out, post the table definition here. Access is similar to SQL except you don't have the table declaration and only one statement per query object. So, if you have multiple statement above, run them on separate query object. You must first create the table and changed the table name of the queries. Hope this helps.

May the Almighty God bless us all!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources