Using group by but not show specific columns

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)

Using group by but not show specific columns

Author

Topic

Rauken
Posting Yak Master

108 Posts

Posted - 2005-01-11 : 11:13:49

Ok I'll try to explain my problem. I have system that handles all the vehicles for a major swedish company. I have a report that lists all the vehicles and the costs on each vehicle. All the costs except fuel costs must be presented in detail. That's no problem. The fuel cost must be grouped per month and then per gas station (company). Example (I've converted to US example):

Chevron/January $543.23
Exxon/January $123.12

I've created a view that lists all the costs in the system, on each row I've added licensno, city etc. On the webpage I just query what I want then loop the result, check if the licensno change then add a new vehicle etc.

Now over to my question. My "base" view has the following fields:
LicenseNo
Organisation
InvoiceNo
TypeOfCost
Company
DateOfPurchase
Cost

I have a new view that tries something like this:

SELECT company, SUM(Cost), DATEPART(month, DateOfPurchase)
FROM view1

Here is where I run into problems. In my stored procedure I need the DateOfPurchase field available to be able to query between dates but I can't have it in my Select becuase it won't do a group, the dates differ. Anyone knows a way around this?

X002548
Not Just a Number

15586 Posts

Posted - 2005-01-11 : 13:16:40

Use a Predicate?

WHERE myDate > @myDate AND myDate < @myOtherDate

?

Brett

8-)

Rauken
Posting Yak Master

108 Posts

Posted - 2005-01-12 : 03:50:03

Ok I my explanation was not that good. In my stored procedure I want to do exactly like wrote WHERE myDate > @myDate AND myDate < @myOtherDate. To be able to do that I must expose myDate in the view. This leads to that the view does not group the data properly. It shows all the records instead of:

January, Chevron, $543.23.

It looks something like this:
January Chevron $12.23 4/12/04
January, Chevron $34.34 5/12/04
.
.

I want my view to Sum the cost and group by month and company but still be able to do WHERE myDate > @myDate AND myDate < @myOtherDate.

Please ask if any questions.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-01-12 : 05:21:18

can you post (more detailed) sample input data and matching expected results?

Rauken
Posting Yak Master

108 Posts

Posted - 2005-01-12 : 07:21:36

Sure!
Base view, let's call it Invoices, it looks like this:


LicenseNo	Organisation	InvoiceNo	TypeOfCost 	Company DateOfPurchase 	Cost 	Description
ABC 123		Org1		2342		Fuel		Chevron	4/12/04		12.34	Regular
ABC 123		Org1		2342		Fuel		Chevron 6/12/04		23.45	Regular
ABC 123		Org1		53422		Fuel		Exxon	7/12/04		34.23	Regular

The database contains 2700 vehicles so it's quite a lot of data. I want to create a stored proc that returns the sum of each vehicle during a specific period of time. The sum must be grouped on Month and Company.

A procedure could look like this:


SELECT 	DATEPART(month, DateofPurchase) AS 'Month',
	Company,
	SUM(Cost) AS 'MonthCost'

FROM 	Invoices 

WHERE 	DateOfPurchase >= @date_from AND DateOfPurchase <= @date_to
AND	TypeOfCost = 'Fuel'
AND	LicenseNo = @license_no
GROUP BY DATEPART(month, DateofPurchase), Company

The problem for me using this procedure is performance. I have a report and it might contain 80 vehicles and it just take too long time to use this procudure 80 times.

What I'm trying to do is make a second view based on the first on that already has calculated the Sum, month etc. My problem is that I need to enter the criteria for DateOfPurchase and that ruins the group by clause.

Rauken
Posting Yak Master

108 Posts

Posted - 2005-01-12 : 07:27:23

Forgot to post the result I want. I want it to look like this:


Month		Company	MonthCost
December	Chevron	35.79
December	Exxon	34.23

This is "fuel data" for licencse no ABC 123 during December.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources