Query help. - 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)

Query help.

Author

Topic

label
Posting Yak Master

197 Posts

Posted - 2003-06-26 : 11:19:03

The following doesn't work because I've got a "group by" in one of my joins that throws a syntax error:

select s.accountnumber, convert(money, sum(totalsalenet)) as [Total]
from 
	sales_history s
join 
	smc_new_products.dbo.etechmodelrequests et 
on 
	s.partnumber=et.configname
		and 
			et.username in 
				(select convert(varchar(50), [user_id]) from smc_new_products.dbo.usr_smc where country='United States')
		and 
			et.username not in 
				(select convert(varchar(50), [user_id]) from etech_portal.dbo.excluded_users)
		and 
			requestdatetime between '04/01/02' and '03/31/03'
		and
			interfacename like '%down%'
		and 
			result=0
		group by et.Configname+convert(varchar(10), et.REquestdatetime, 101)+et.Username
join 
	smc_etech_key sk
on 
	et.username=convert(varchar(50), sk.etech_customer_id)
and 
	purchase_date between 20020401 and 20030401
group by s.accountnumber

The problem is, if I take the group by out of the second table join, I may get several "et.configname" returned and I only want 1 in order to correctly calculate sales.

I'm trying to calculate sales, grouped by account number where the partnumber in the saleshistory table is the same as that in the etechmodelrequest table and that modelrequest was downloaded by a user whose matched up in the smc_etech_key table.

Any ideas how I can get just the distinct/grouped by data?

Thanks

Edited by - label on 06/26/2003 14:06:52

Edited by - label on 06/26/2003 14:10:08

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-06-26 : 11:32:50

I must admit, I can't follow your SQL statement at all...

however, whenever I see this:

group by et.Configname+convert(varchar(10), et.REquestdatetime, 101)+et.Username

that raises a red flag. Don't group by an expression like this, group by the values needed to create the expression.

i.e.,

select Name + ',' + convert(varchar(20),Age) + ',' + convert(varchar(10), SomeDate, 101))
from
table
group by Name, Age, SomeDate

don't group by the expression you are *returning*, group by the values need to *form* that expression. Don't bother converting in the group by, either.

that doesn't solve your problem in the slightest, but it is (hopefully) a good overall tip.

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-06-26 : 11:35:59

can you re-print your sql with some [code] tags so that we can see the indenting? and add as much helpful indenting to it as you can.

That would help me out quite a bit.

thanks!

- Jeff

label
Posting Yak Master

197 Posts

Posted - 2003-06-26 : 14:09:09

quote:

group by et.Configname+convert(varchar(10), et.REquestdatetime, 101)+et.Username

that raises a red flag. Don't group by an expression like this, group by the values needed to create the expression.

I have to in this case to make sure I'm only getting 1 instance of a specific user+config+partnumber in a single day.

quote:
select Name + ',' + convert(varchar(20),Age) + ',' + convert(varchar(10), SomeDate, 101))
from
table
group by Name, Age, SomeDate

That wouldn't return the results I need. I have to have a single configname returned for every instance in a single day that unique person downloads a specific part in that day.

Edited by - label on 06/26/2003 14:09:53

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-06-26 : 14:39:28

Label,
You need an inner select there. You can't just throw a GROUP BY in a join clause.

Reformatting your SQL to see what you're trying to do, I agree with Jeff - it's completely unclear. I would suggest describing in pseudocode what you want your rowset to look like and we can help you from there.

Jonathan
{0}

dsdeming

479 Posts

Posted - 2003-06-26 : 21:59:56

This SQL is kind of tough to follow, but you may be able to replace smc_new_products.dbo.etechmodelrequests with a derived table based on a SELECT DISTINCT columnlist that table. That could eliminate your problem with multiple confignames.

Dennis

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-06-27 : 07:55:50

label -- first, for the group by's -- that exactly what the statement I gave you will do. it will give you unique values because you are grouping by all 3 fields. Before you assume something won't work because it looks different, try it out! you never know....

you say you want 1 instance of a user/partnumber/day so you use:

group by et.Configname+convert(varchar(10), et.REquestdatetime, 101)+et.Username

but that is EXACTLY what

group by et.Configname, et.REquestdatetime, et.Username

returns ! again, try it out. the problem with doing your grouping's that way (other than lack of clarity and worse performance) is if you have this data:

configname: Config1
date: 1/1/2000
user: Jeff

configname: Config
date: 11/1/2000
user: Jeff

they will BOTH group into Config11/1/2000Jeff -- but they are different configurations! I know this is a bad example with your data, but trust me -- it is not the way to go. you can't assume that this will never happen in your data, because one day it will and it'll take you days to figure out if you don't do it right the first time. plus it is MUCH more efficient to group by "natural" columns instead of formulas. again, try it out. That's what GROUP BY is for!

same thing with my name/age/date example -- it WILL do exactly what you said it won't do. that was just an example, and not specific to your data, I hope that was clear. If not, sorry for the confusion! i was just trying to teach you a general technique that you could apply elsewhere.

as for your sql statment, it still makes no sense ... i will give it a shot later on today.

- Jeff

Edited by - jsmith8858 on 06/27/2003 07:57:08

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources