January 8, 2013 at 3:00 pm
Thanks in advance for helping with this.
I have three tables they are:
BOM_BAR BB
BOM_BAR_CUT BBC IS A MANY TO ONE TO BB
BOM_CUTPLAN_PIECE BCP WHICH IS A ONE TO ONE TO BBC
Current SQL that I have is simple and is as follows:
Select BB.BOM_BAR_ID, BB.BAR_LENGTH, BBC.QUANTITY, BCP.PIECE_LENGTH
FROM BOM_BAR BB
JOIN BOM_BAR_CUT BBC ON BBC.BOM_BAR_CUT_ID = BB.BOM_BAR_CUT_ID
JOIN BOM_CUTPLAN_PIECE BCP ON BCP.BOM_CUTPLAN_PIECE_ID = BBC.BOM_CUTPLAN_PIECE_ID
WHERE BB.BOM_CUTPLAN_ID = 337332
GROUP BY BB.BOM_BAR_ID, BB.BAR_LENGTH, BBC.QUANTITY, BCP.PIECE_LENGTH
Resulting data is:
BOM_BAR_IDBAR_LENGTHQUANTITYPIECE_LENGTH
4756732216.000000000000000149.562500000000000
4756732216.000000000000000355.062500000000000
4756733216.000000000000000149.562500000000000
4756733216.000000000000000355.062500000000000
4756734216.000000000000000149.562500000000000
4756734216.000000000000000355.062500000000000
4756735216.000000000000000149.562500000000000
4756735216.000000000000000355.062500000000000
4756736216.000000000000000149.562500000000000
4756736216.000000000000000355.062500000000000
4756737216.000000000000000149.562500000000000
4756737216.000000000000000355.062500000000000
4756738216.000000000000000149.562500000000000
4756738216.000000000000000355.062500000000000
4756739216.000000000000000149.562500000000000
4756739216.000000000000000355.062500000000000
4756740216.000000000000000149.562500000000000
4756740216.000000000000000155.062500000000000
4756740216.000000000000000255.062500000000000
4756741216.000000000000000149.562500000000000
4756741216.000000000000000355.062500000000000
4756742216.000000000000000149.562500000000000
4756742216.000000000000000355.062500000000000
4756743216.000000000000000149.562500000000000
4756743216.000000000000000355.062500000000000
4756744216.000000000000000149.562500000000000
4756744216.000000000000000355.062500000000000
4756745216.000000000000000149.562500000000000
4756745216.000000000000000355.062500000000000
4756746216.000000000000000149.562500000000000
4756746216.000000000000000355.062500000000000
4756747216.000000000000000149.562500000000000
4756747216.000000000000000355.062500000000000
4756748216.000000000000000149.562500000000000
4756748216.000000000000000355.062500000000000
4756749216.000000000000000155.062500000000000
4756749216.000000000000000349.562500000000000
4756750216.000000000000000449.562500000000000
4756751216.000000000000000449.562500000000000
4756752216.000000000000000449.562500000000000
4756753216.000000000000000449.562500000000000
4756754216.000000000000000449.562500000000000
4756755216.000000000000000449.562500000000000
4756756216.000000000000000449.562500000000000
4756757216.000000000000000149.562500000000000
4756757216.000000000000000349.562500000000000
4756758216.000000000000000449.562500000000000
4756759216.000000000000000449.562500000000000
4756760216.000000000000000449.562500000000000
4756761216.000000000000000449.562500000000000
4756762216.000000000000000449.562500000000000
4756763216.000000000000000449.562500000000000
4756764216.000000000000000449.562500000000000
4756765216.000000000000000449.562500000000000
4756766216.000000000000000449.562500000000000
4756767216.000000000000000449.562500000000000
4756768216.000000000000000449.562500000000000
4756769216.000000000000000449.562500000000000
4756770216.000000000000000249.562500000000000
4756770216.000000000000000426.937500000000000
4756771216.000000000000000726.937500000000000
4756772216.000000000000000726.937500000000000
4756773216.000000000000000726.937500000000000
4756774216.000000000000000726.937500000000000
4756775216.000000000000000726.937500000000000
4756776216.000000000000000726.937500000000000
4756777216.000000000000000226.937500000000000
4756777216.000000000000000526.937500000000000
4756778216.000000000000000726.937500000000000
4756779216.000000000000000726.937500000000000
4756780216.000000000000000726.937500000000000
4756781216.000000000000000726.937500000000000
4756782216.000000000000000726.937500000000000
4756783216.000000000000000726.937500000000000
4756784216.000000000000000726.937500000000000
4756785216.000000000000000726.937500000000000
4756786216.000000000000000726.937500000000000
4756787216.000000000000000726.937500000000000
4756788216.000000000000000226.937500000000000
4756788216.000000000000000526.937500000000000
4756789216.000000000000000726.937500000000000
4756790216.000000000000000726.937500000000000
4756791216.000000000000000726.937500000000000
4756792216.000000000000000326.937500000000000
What I would like to do is to roll the data to look like the following:
COUNT
8 1 49.5625
3 55.0625
I want to Count the occurances of BOM_BAR_ID and summarize the PIECE_LENGTH per BOM_BAR_ID. I want all the like LENGTHS to be together. Each BOM_BAR_ID represents a piece of material and the PIECE_LENGTH represents the sub pieces out of the BOM_BAR_ID. So the data shows that we have multiple BOM_BAR_ID with the same PIECE_LENGTHS. I want to show a count of BOM_BAR_ID with the unique PIECE_LENGTHS.
SQL 2008
Thanks again in advance
Charles
January 8, 2013 at 3:13 pm
I want to Count the occurances of BOM_BAR_ID and summarize the PIECE_LENGTH per BOM_BAR_ID. I want all the like LENGTHS to be together. Each BOM_BAR_ID represents a piece of material and the PIECE_LENGTH represents the sub pieces out of the BOM_BAR_ID. So the data shows that we have multiple BOM_BAR_ID with the same PIECE_LENGTHS. I want to show a count of BOM_BAR_ID with the unique PIECE_LENGTHS.
What does summarize the PIECE_LENGTH mean? It is very unclear what you are trying to do here. I think you probably want grouping with some aggregate data but it is hard to tell.
If you could post ddl and sample data in a consumable format we can help. Please take a look at the first link in my signature for best practices when posting questions.
_______________________________________________________________
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/
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply