Aggregates and regular field name in SQL

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)

Aggregates and regular field name in SQL

Author

Topic

ASPSQLVB
Starting Member

46 Posts

Posted - 2006-11-18 : 01:11:13

Guys, I am stuck here.....been trying to select the field name "OZ" in this SQL STATEMENT along with the aggregates.

How is this done.....thank you all for your time.

SELECT SUM(Lbs) AS SumLbs,
Max(Oz) as MaxOz,
SUM(Oz) AS SumOz,
MAX(Lbs) as MaxLbs
FROM Fish Where FishId=232

Kristen
Test

22859 Posts

Posted - 2006-11-18 : 01:13:32

This perhaps?


SELECT OZ,
       SUM(Lbs) AS SumLbs,
       Max(Oz) as MaxOz,
       SUM(Oz) AS SumOz,
       MAX(Lbs) as MaxLbs 
FROM Fish 
Where FishId=232
GROUP BY OZ

Kristen

ASPSQLVB
Starting Member

46 Posts

Posted - 2006-11-18 : 01:18:26

Hi Kristen,

I tried that. Doesn't give me just a single row....I am gettin 4 rows when I only need one.
Thanks for trying though.

Kristen
Test

22859 Posts

Posted - 2006-11-18 : 01:52:26

Presumably you are getting 4 different value for OZ then?

In which case you'll have to redefine your question please. What value of OZ are you wanting to see?

A sample of some input data and the expected results you want would help

Kristen

ASPSQLVB
Starting Member

46 Posts

Posted - 2006-11-18 : 02:02:48

I would like to see just one row displayed

Oz, Sum(Lbs), Sum(Oz), Max(Lbs), Max(Oz)

When I execute this statement I get 4 rows of data.
SELECT OZ,
SUM(Lbs) AS SumLbs,
Max(Oz) as MaxOz,
SUM(Oz) AS SumOz,
MAX(Lbs) as MaxLbs
FROM Fish
Where FishId=232
GROUP BY OZ

Kristen
Test

22859 Posts

Posted - 2006-11-18 : 02:13:17

But OZ has four distinct values, which value do you want to see in your

Oz, Sum(Lbs), Sum(Oz), Max(Lbs), Max(Oz)

example?

Please post the output of the 4 rows you are getting - it might help me understand what you are seeing a bit better!

Kristen

ASPSQLVB
Starting Member

46 Posts

Posted - 2006-11-18 : 02:17:57

SELECT OZ,SUM(Lbs) AS SumLbs,Max(Oz) as MaxOz,SUM(Oz) AS SumOz, MAX(Lbs) as MaxLbs FROM Fish
Where FishId=232
GROUP BY OZ

OZ SumLbs MaxOz SumOz MaxLbs

5 48.00 5 5 48.00
6 108.00 6 36 18.00
7 20.00 7 14 10.00
15 36.00 15 60 9.00

Kristen
Test

22859 Posts

Posted - 2006-11-18 : 02:23:22

So OZ has values of 5, 6, 7 and 15

Which of those values do you want to see in your "single row"?

Please provide an example of the output you want, as I have already requested.

Kristen

ASPSQLVB
Starting Member

46 Posts

Posted - 2006-11-18 : 02:30:08

OZ........SumLbs..........MaxOz.......SumOz.........MaxLbs
5...........108............15..........60..........48.00

Kristen
Test

22859 Posts

Posted - 2006-11-18 : 02:38:17

[code]
SELECT MIN(OZ),
SUM(Lbs) AS SumLbs,
Max(Oz) as MaxOz,
SUM(Oz) AS SumOz,
MAX(Lbs) as MaxLbs
FROM Fish
Where FishId=232
[/code]
But given your data:
[code]
OZ SumLbs MaxOz SumOz MaxLbs
5 48.00 5 5 48.00
6 108.00 6 36 18.00
7 20.00 7 14 10.00
15 36.00 15 60 9.00
[/code]
SumLbs = 108 would only be the value for OZ=6
SumOz = 60 would only be the value for OZ=15

so I'm still not clear what you want as the result, and whether indeed you want the Minimum value of OZ in the first column?

Kristen

ASPSQLVB
Starting Member

46 Posts

Posted - 2006-11-18 : 02:47:27

I am trying to get the Max Lbs and the oz field along with that where the FishId = 232

Im not trying to get the min OZ .....I need the Max Lbs and the Oz. If that helps

Kristen
Test

22859 Posts

Posted - 2006-11-18 : 03:34:29

No, I'm afraid it doesn't help. Either I'm being thick or you have not explained the problem clearly.

You have 4 values for OZ. I have no idea why you want OZ=5 to be displayed (other than that it is the minimum value, which you say you don't want).

You need to post a CREATE TABLE statement, INSERT statements for some sample data, and an example of the output you need, and then hopefully we can work out what it is that you are trying to achieve.

Here is an example:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74221

Kristen

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2006-11-18 : 06:36:20

Maybe it's a case sensitive database and there are two columns called Oz and OZ, respectively?
Clutching at straws.

ASPSQLVB
Starting Member

46 Posts

Posted - 2006-11-18 : 11:43:53

Kristen,

there is a column Lbs and a column Oz..........I want the MAX(lbs) and the Oz that goes with that Max(Lbs) from the Fish table were the FishId = 232.

ASPSQLVB
Starting Member

46 Posts

Posted - 2006-11-18 : 20:53:42

FIGURED IT OUT.......
...Select * from Fish Where Lbs=(Select Max(Lbs) from Fish Where FishId = 232)

ASPSQLVB
Starting Member

46 Posts

Posted - 2006-11-20 : 21:04:21

Guys,

I know this statement is not allowed
Select FishId,Oz,Max(Lbs) as MaxLbs from Fish Where FishId = 232

So, how can I get the field "OZ" that goes with the Max(Lbs) Where Fish Id = 232 ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-20 : 21:28:54

[code]
select *
from Fish f
inner join
(
select FishId, Max(Lbs) as MaxLbs
from Fish
group by FishId
) m
on f.FishId = m.FishId
where f.FishId = 232
[/code]

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-11-20 : 21:29:27

You have posted neither the table strtucture (DDL) nor a sample of the data in that table.

Please post that, along with the output result that you expect.

CODO ERGO SUM

ASPSQLVB
Starting Member

46 Posts

Posted - 2006-11-20 : 21:40:44

This link I hope sums up what I am trying ot do
http://www.dbtalk.net/microsoft-public-sqlserver-programming/aggregate-functions-345096.html

Select Lbs, Oz FROM Fish
where Lbs = (select max(Lbs) from Fish where fishid = 232)

There are 2 Max(Lbs) coming back.....they are both 29 lbs.....
I would like to get the max(Oz) of the two 29 lb Max values that are coming back.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-20 : 21:58:39

" This link I hope sums up what I am trying ot do"
Maybe it does. But it will make things much clearer if you can post your table DDL, some sample data and the result that you want.

ASPSQLVB
Starting Member

46 Posts

Posted - 2006-11-20 : 22:13:01

I dont know how to give you what your asking for...How do I insert an image?

I click on insert image but no browse button

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources