How would I do this best? - 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

SQL Server Development (2000)

How would I do this best?

Author

Topic

label
Posting Yak Master

197 Posts

Posted - 2003-03-21 : 10:11:39

I'd like to have the following query perform exactly as it does right now, except that I want to return distinct "config"'s.

quote:
select totalsalenet from zz_nmpg
where config in
(select zzn.config from zz_nmpg zzn inner join zz_etech_info zze on zze.config=zzn.config
where purchase_date > download_date)
and totalsalenet > 0
order by totalsalenet
compute sum(totalsalenet), avg(totalsalenet)

Like I said, it works perfectly for me, I just want it to select a distinct config from the zz_nmpg table.

Thanks in advance.

SamC
White Water Yakist

3467 Posts

Posted - 2003-03-21 : 12:51:35

If there are multiple configs, do you want the Min/Max or Ave of totalsalenet for each distinct config?

You might try group by..

select totalsalenet
from
(
select config, Max(totalsalenet) as totalsalenet
from zz_nmpg
inner join zz_etech_info zze on zze.config = zzn.config and purchase_date>download_date and totalsalenet>0
GROUP BY config
) kayak
order by totalsalenet
compute sum(totalsalenet), avg(totalsalenet)

Sam

Completely revised my post. The first solution was wrong.

Edited by - SamC on 03/21/2003 13:04:44

edited again - Dang! - forgot the group by

Edited by - SamC on 03/21/2003 13:07:42

label
Posting Yak Master

197 Posts

Posted - 2003-03-21 : 13:00:21

quote:

select totalsalenet from zz_nmpg
where config in
(select DISTINCT zzn.config from zz_nmpg zzn inner join zz_etech_info zze on zze.config=zzn.config
where purchase_date > download_date)
and totalsalenet > 0
order by totalsalenet
compute sum(totalsalenet), avg(totalsalenet)

I tried this as well initially but it didn't work.

When I thought about why it wouldn't, I came up with "why would this give me a distinct config name in my primary query? Won't my primary query still select all of the config names from the table even if the subquery only has one mention of it?

For instance, if I have 5 instances of the config "abcd" in the table zz_nmpg, isn't the above query going to pull all 5 instances of those "abcd" config's because even though I only have one instance in my subquery, it only needs that one instance to pull all 5 in the first place.

I'm not sure if I'm being clear or not but hopefully that'll make things clearer.

Thanks.

(edit: sorry, I replied before you had retracted your solution. I'll leave my comments up though as they will hopefully clarify my dilema)

Edited by - label on 03/21/2003 13:02:33

SamC
White Water Yakist

3467 Posts

Posted - 2003-03-21 : 13:09:41

Sorry I couldn't get it right - took two revisions till I couldn't see a mistake. You might want to change MAX to MIN, SUM or AVE.

I'm leaving the office for an hour. Hope this works. Let me know.

Sam

One More edit - This query might work too, but I'm not sure group by and compute will work in the same query.

select Max(totalsalenet) as totalsalenet
from zz_nmpg
inner join zz_etech_info zze on zze.config = zzn.config and purchase_date>download_date and totalsalenet>0
GROUP BY config
order by totalsalenet
compute sum(totalsalenet), avg(totalsalenet)

Edited by - SamC on 03/21/2003 13:12:17

label
Posting Yak Master

197 Posts

Posted - 2003-03-21 : 13:11:32

quote:

If there are multiple configs, do you want the Min/Max or Ave of totalsalenet for each distinct config?

No, we want every totalsalenet, not grouped by. There may be identical sales for completly different config's in which case the group by totalsalenet would give us bad data.

SamC
White Water Yakist

3467 Posts

Posted - 2003-03-21 : 13:15:20

Now I'm confused. You have 4 configs "abcd" each has a different totalsalenet. Can you list the source data and the desired result of the query?

It sounds from your post that you only want "abcd" to appear once, but you want all the totalsalenets. An example would clear this up.

Sam

label
Posting Yak Master

197 Posts

Posted - 2003-03-21 : 13:38:18

Alright....your revised query seems to return the proper results. Thanks for the advice.

However, I do have to ask, what is "kayak"?

I've looked it up and found nothing in the SQL reference books online??

Edited by - label on 03/21/2003 13:38:35

SamC
White Water Yakist

3467 Posts

Posted - 2003-03-21 : 16:10:36

kayak is an unsupported SQL feature.

Sam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources