Need help with counting - 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

General SQL Server Forums

New to SQL Server Programming

Need help with counting

Author

Topic

marc92
Starting Member

5 Posts

Posted - 2009-10-15 : 16:44:18

Howdy folks,

I'm very new to SQL and trying to learn as I go. What I have is a flat table that lists trucks, the company that owns the truck, truck Make/Model and the filters(oil, air, fuel) that each truck uses along with a quantity for each filter. I am trying to create a query/stored procedure/magic spell that will take the company name as an input and give me a list of filters used and a total of each filter needed for that company's fleet.

I've been able to get a simple count of each filter, but only by hard coding the filter number into the query.


SELECT COUNT(*) AS LAF1849
FROM dbo.TruckList
WHERE Company = 'ABC' AND PrimaryAir = 'LAF1849'

While this works, its cumbersome and doesn't give me a nice list of all the filters used. I'm not asking for someone to write out the entire query, just some pointers on how to get started with this.

If I need to provide more information, please let me know. Thank you very much for any assistance.

X002548
Not Just a Number

15586 Posts

Posted - 2009-10-15 : 17:09:08

quote:
nice list of all the filters used.



SELECT COUNT(*) AS LAF1849
FROM dbo.TruckList
WHERE Company = 'ABC' AND PrimaryAir = 'LAF1849'

This means that you only want the 1 filter...when you say a list of all filters...do you mean


SELECT DISTINCT PrimaryAir
FROM dbo.TruckList
WHERE Company = 'ABC'

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

marc92
Starting Member

5 Posts

Posted - 2009-10-16 : 14:49:12

Yes, Thank you Brett.

Came up with this:


SELECT DISTINCT PrimaryAir, count(PrimaryAir) as AirFilters
	From dbo.TruckList
	Where Company = 'Southern Star'
	GROUP BY PrimaryAir

Which is exactly what I'm needing. Lists each distinct primary air filter and the number of each. Why on earth I didn't think to look for a DISTINT or UNIQUE keyword I will chalk up to lack of caffiene yesterday. Now to create a formated view to show all this info for each filter and I'm set.

Thanks again!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources