sql grouping

[stucker79]

I'm working on a report for my boss. I am grabbing info from a table that has the following fields:

ItemID
Item
ItemAmt
Quantity
Total (which is a calculated value of itemamt * quantity)
MainCategoryID
ProjectID
Notes
Deleted (a bit field indicating whether the record is "deleted")(users here have a tendency to delete things they shouldn't so this is an easy way to get the records back)

I need to do the following:

Group items by Project, then MainCategory and order by the most expensive project first. Within each project grouping, I need to order by the most expensive main cateogory first. For example:

Project A $500,000
MainCategory A $300,000
Item A $160,000
Item B $140,000
MainCategory B $200,000
Item A $120,000
Item B $80,000
Project B $400,000
MainCategory A $250,000
Item A $130,000
Item B $120,000
MainCategory B $150,000
Item A $120,000
Item B $30,000

Then, here's the kicker. All items with no project (projid = 27) have to show up all the way at the bottom of the list, ordered the same way.

Is this even possible?

 

[maswien]

Project A $500,000
MainCategory A $300,000
Item A $160,000
Item B $140,000
MainCategory B $200,000
Item A $120,000
Item B $80,000
Project B $400,000
MainCategory A $250,000
Item A $130,000
Item B $120,000
MainCategory B $150,000
Item A $120,000
Item B $30,000

select project, MainCategory, itemid,grouping() sum(...)
from MyTable
group by project, MainCategory, itemid
with rollup

 

[maswien]

select
project,grouping(project),
MainCategory, grouping(MainCategory),
itemid,grouping(itemid),
sum(...)
from MyTable
group by project, MainCategory, itemid
with rollup

 

[stucker79]

is the sum(...) literal or am I supposed to put sum(project, maincategory, itemid) in there?

 

[stucker79]

ok i tried putting sum(calc) in which is the field i need the sum on. It kind of worked. It didn't order them in order of most expensive and it repeats some of the records? sorry, advanced scripts are new to me!! thanks!!

 

[stucker79]

Ignore what I said about it repeating records, i was looking at it icorrectly. This is great! The only thing left is the ordering.