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 2005 Forums

Transact-SQL (2005)

query help

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-07-15 : 06:07:56

I have
select i.institution_name as Institution,e.first_name as 'First Name',e.last_name as 'Last Name',sum(total_price) as 'Total Amount', count(order_id) as 'Number of orders' from order_info oi left join enduser_info e on e.enduser_id=oi.enduser_id left join institutions i on i.institution_id=e.institution_id WHERE order_date>='06/01/2008' and order_date<='07/01/2008'
group by i.institution_name,e.first_name,e.last_name,sum(total_price)

How can I add to this where clause
where sum(total_price)>300 and sum(total_price)<5000

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-15 : 06:11:48

At the end, include

Having sum(total_price)>300 and sum(total_price)<5000

Madhivanan

Failing to plan is Planning to fail

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-07-15 : 06:22:01

thanks - that's what i needed
how can i format the sum(totalamount) to 2 decimal points.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-15 : 06:26:54

Have a look at ROUND function.

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-15 : 06:28:37

quote:
Originally posted by esthera

thanks - that's what i needed
how can i format the sum(totalamount) to 2 decimal points.

Cast to decimal(16,2)

Madhivanan

Failing to plan is Planning to fail

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-07-15 : 06:50:58

round(sum(total_price),2) as 'Total Amount',
didn't work
how do i use cast to decimal

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-15 : 06:54:41

quote:
Originally posted by esthera

round(sum(total_price),2) as 'Total Amount',
didn't work
how do i use cast to decimal

What do you mean by "Didn't work"?
Post some sample data with expected result

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-15 : 07:05:16

It's a presentation issue.
He needs two decimals always.

E 12°55'05.25"
N 56°04'39.16"

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-07-15 : 07:13:17

yes

my example would be 100.20 and not 100.255555555

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-15 : 07:15:28

cast(sum(total_price) as decimal(16,2))

Madhivanan

Failing to plan is Planning to fail

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-07-16 : 07:04:51

thanks..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources