Drill down values...

Question

Post reply

Drill down values...

PiMané

SSChampion

Points: 13965
More actions
November 3, 2016 at 7:09 am

#315206

Hi,
I've an orders table with customer and supplier (customer_id, supplier_id, order_id, order_date, ...)
Is it possible to get the total orders from a customer and drill down to the supplier in the same query?
Possible output:
customer | supplier | total orders
c1 | | 4
c1 | s1 | 2
c1 | s2 | 1
c1 | s3 | 1
c2 | | 2
c2 | s2 | 2
Thanks,
Pedro

If you need to work better, try working less...

Viewing 8 posts - 1 through 7 (of 7 total)

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

FridayNightGiant SSCertifiable Points: 7151 More actions · Answer 1

I think you want to look at rollups. Check this out for a very quick example.

There is a lot of information at the link below

https://technet.microsoft.com/en-us/library/bb522495(v=sql.105).aspx

CREATE TABLE #Orders (

ID INT IDENTITY(1, 1)

, Customer CHAR(2)

, Supplier CHAR(2)

);

INSERT INTO #Orders (

Customer

, Supplier

)

VALUES ( 'C1' -- Customer - char(2)

, 'S2' -- Supplier - char(2)

)

, ( 'C1' -- Customer - char(2)

, 'S2' -- Supplier - char(2)

)

, ( 'C1' -- Customer - char(2)

, 'S3' -- Supplier - char(2)

)

, ( 'C2' -- Customer - char(2)

, 'S2' -- Supplier - char(2)

);

SELECT Customer

, Supplier

, COUNT(1)

FROM #Orders

GROUP BY ROLLUP(Customer, Supplier)

ORDER BY Customer

, Supplier;

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 2

PiMané (11/3/2016)
Is it possible to get the total orders from a customer and drill down to the supplier in the same query?

Yes. You can use GROUP BY WITH ROLLUP or any number of different "Grouping" settings.

[EDIT] FridayNightGiant beat me to it while I was camped out on the post getting ready to write a reply.

--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)

PiMané SSChampion Points: 13965 More actions · Answer 3

Thank you all for your help

5*

I knew I've seen it somewhere but couldn't remember quite well..

If you need to work better, try working less...

PiMané SSChampion Points: 13965 More actions · Answer 4

Hi,

Is there a way to "tell" ROLLUP that 2 fields are to be "joined"?

For example, if supplier has a name can I join with the suppliers table and get it's name associated with supplier_id and rollup will treat these fields as "one"?

Or do I have to get the ROLLUP with just the ids and join that with the suppliers?

Thanks,

Pedro

If you need to work better, try working less...

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 5

PiMané (11/8/2016)
Hi,
Is there a way to "tell" ROLLUP that 2 fields are to be "joined"?
For example, if supplier has a name can I join with the suppliers table and get it's name associated with supplier_id and rollup will treat these fields as "one"?
Or do I have to get the ROLLUP with just the ids and join that with the suppliers?
Thanks,
Pedro

Either will work but the latter stands a good chance of performing better due to "pre-aggregation".

--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)

PiMané SSChampion Points: 13965 More actions · Answer 6

Jeff Moden (11/8/2016)
Either will work but the latter stands a good chance of performing better due to "pre-aggregation".

I used the 2nd option.... A CTE with the rollup data and then joined...

It works fine 🙂

Thanks,

Pedro

If you need to work better, try working less...

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 7

PiMané (11/8/2016)
Jeff Moden (11/8/2016)
Either will work but the latter stands a good chance of performing better due to "pre-aggregation".
I used the 2nd option.... A CTE with the rollup data and then joined...
It works fine 🙂
Thanks,
Pedro

Just another tip... there are times when the "natural" sort order of a ROLLUP can be interfered with whether you used a CTE or not. If you add an OVER clause to the query, the "natural" sort order will be destroyed. If you've used preaggregation (as with a CTE) and if the optimizer decides to use the order of an index on a column joined in the outer SELECT, the "natural" order could be destroyed.

To make the sort order "bullet proof", you may want to add a final sort based on a combination of "GROUPING" (a function of ROLLUP/CUBE and GROUPINGs). If any additional overhead is incurred by the final sort, it's usually trivial if the ROLLUP produces a "blocking" operator that causes the rollup to materialize before the sort.

I did do a presentation where I proved that GROUP BY in a CTE (without a ROLLUP) is not a guarantee of preaggregation prior to a sort. A lot of people make the mistake of thinking that CTEs are resolved in the same top-down order in which they are written and they are frequently not.

With that in mind, what I'll normally do is two GROUP BYs... one in the CTE and then one in the outer SELECT with the ROLLUP and any joins that need to be incurred and is very fast. The first time I saw this "preaggregation" method was by a fellow that coined the term of "preaggregation", Peter "Peso" Larsson. It can actually double the performance of things like CROSSTABs and ROLLUPs. A decent example of the technique is included in the performance testing of the following CROSSTAB article. It also virtually eliminates the need for final sorts of ROLLUPs in the outer query unless, again, there's a different sort in the form of OVER, etc.

[font="Arial Black"]Cross Tabs and Pivots, Part 1 – Converting Rows to Columns[/font][/url]

--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)