Looking for an aggregate function but can't get it quite right.

Question

Looking for an aggregate function but can't get it quite right.

FunkyDexter

Hall of Fame

Points: 3907
More actions
January 29, 2013 at 5:04 am

#273454

Hi All
I think I can achieve my goal with an aggregate function but I just can't seem to get it quite right. I'm hoping someone can point me in the right direction. I have a dataset that looks something like this:-
project order qty (a bunch of other fields that aren't really relevant)
1 1 5
1 2 5
1 3 5
2 1 10
2 2 10
3 1 7
3 2 7
3 3 7
4 4 7
A project has one to many orders. The qty applies to a project, not an order. I now want a total qty for the whole report. In the above example that would be 22 (5 + 10 + 7). Unfortunately a straight sum would give me 63.
I've tried to skin this particular cat a few ways but none is giving me quite what I want.
1. Most of the extra fields relate to order and I want a drilldown so I do want the data back in this "nested" format of projects and orders
2. I tried only putting the qty on on row per project but the ordering is deliberately indeterminate in the report so this was problematic when trying to make sure it showed the qty ("First" would often show a blank because it picked up the wrong row) and what it did to the query was franky rancid.
3. I've tried playing around with various divisions of count distinct but can't seem to get a correct answer out of that.
4. Finally I've tried playing with a hidden running total but can't seem to manage that either.
Can anyone point me in the right direction on this because I'm completely stuck and it feels like the sort of thing that would be covered by a simple aggregate function?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Please read and follow the instructions found by reading the article in my signature about best practices. Nobody can help because we don't have ddl, sample data, desired output.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

abdulkalam.m SSC Enthusiast Points: 168 More actions · Answer 2

Hi,

As per my knowledge i will create a summary and detailed reports (drill through),

summary report with project, count(orders) and quantity. and detailed report with project, order and other details .

sorry if it wont work for you.

Jeff Moden SSC Guru Points: 1004166 More actions · Answer 3

Actually, for the data given, the correct answer is 29 because of project 4.

SELECT Total = SUM(d.Qty)

FROM (

SELECT Qty = MIN(Qty)

FROM #TestTable

GROUP BY Project

) d

You're kind of new so let me tell ya.. if you post your data in a readily consumable format instead of like you did, you'll get better answers quicker and they'll usually be tested coded answers. Like Sean suggested, read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

FunkyDexter Hall of Fame Points: 3907 More actions · Answer 4

Actually, for the data given, the correct answer is 29 because of project 4

Damn, that was was a typo and supposed to be project 3:blush:

Sorry I wasn't clear in my original post. I'm not after a SQL aggregate function, that's easy. I'm hoping there's am aggregate function I can use in the SSRS report designer to save bringing back an extra piece of data from the DB that could be calculated client-side. It's not a huge deal if it can't be done, I'll just add a sub query to the sql to put the total qty onto every row and then use First in the Report definition but I was hoping for something more elegant.