Counting/grouping/etc/ - 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)

Counting/grouping/etc/

Author

Topic

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-08-06 : 09:25:48

Hi, I have two tables which are joined by ZMproducts.productlocationid and ZMLocation.LocationId:
- ZMProducts (with columns productid, productname, quantity, productlocationid productcategoryid, etc....)
- ZMLocation (with colums locationid, locationname, locationprovinceid)

- Each product has 1 productcategoryid which links to another table with categories
- Each location has 1 locationprovinceid which links to another table with provinces

Now I need to make an overview of products by category, and products by provinces and a combination of this.

Now I already have a SP for every category GetProductsByCategoryID
And I already have a SP for every province GetProductsByProvinceID

But I think this can be done better to make an overview like this:

Category Number of products Quantity
cat1 5 20
cat2 2 100
cat3 4 50

TOTAL 11 170

And I must also split them for each province
so:

Province1
Category Number of products Quantity
cat1 1 10
cat2 2 100

Total 3 110

Province2
Category Number of products Quantity
cat1 4 10
cat3 4 50

Total 8 60

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-08-06 : 09:31:33

Wouldn't this be called a Pivot query or something?
Don't know much about it.
has anyone got any clues for me...

The secret to creativity is knowing how to hide your sources. (Einstein)

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-06 : 09:48:51

from the way you've shown your required result, it looks as though what you really want is to write a report with seperate groups / totals etc. this is not something to be done in t-sql. you can group it by province / category etc, but not split it out in the way you have shown

what are you using for your front end?

Em

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-08-06 : 09:58:24

I'm building in visual basic . net.

And I was indeed thinking of looping through some lists a couple of times and executing some SP's. But I am looking at a nice way of doing this.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-06 : 13:16:54

You could return the data in the following format

Province Category Number of products Quantity
Province1 cat1 1 10
Province1 cat2 2 100
Province1 null 3 110
Province2 cat1 4 10
Province2 cat3 4 50
Province2 null 8 60

and then use your front end app to format it in order you want.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources