Help with Query

Hello and Thanks in advance,

I am trying to get the percentage that each row contributes to the total rows for a given time frame on a given line (Select ScrapCat, ScrapLbs/Sum(ScrapLbs ... Group by Category, LineNum) as percentage). We have 11 categories for each line for each day. The percentage for each category would be the sum of all rows for that category for that line that time frame divided the sum of all rows for all categories for that line and time frame.

A return would look like this
Category......ProLine.......ScrapLbs.....Sum( ScrapLbs)...Percentage...
CateA----1-----.25----2.0-----.125--
CateB----1-----.35----2.0-----.175--
CateC----1-----.5----2.0-----.25--
etc
CateA----2-----.25----1.0-----.25--
CateB----2-----.50----1.0-----.5--
etc
Table looks like this

ProDate ..............smalldatetime
ProLine ...............int
Category.............char
ScrapLbs ............float
ProShift ..............int

is this possible?
Thanks,
LeeCan you post the DDL and some sample base data

Like CREATE TABLE myTable99 (Col1 int, ..ect

And sample data that would put the data in to the table, like

INSERT INTO myTable99 (Col1, col2, ect)
SELECT yada, yada, yada UNION ALL
SELECT yada, yada, yada UNION ALL
SELECT yada, yada, yada

That way we can execute the code, set up a test bed and figure it out...

but this kinda throws me right away..

ScrapLbs.....Sum(ScrapLbs)...

How can you have the sum of something, and 1 occurance of something on the same row?|||Sorry my boss shifted my focus!

Hopefully this is what you need.

What I need to do is sum all the scrap for each line for the date range (Sum( lbs) as LineTotal group by line then sum(Category) as EachCategory group by line and Category then divide EachCategory by LineTotal

EachCategory/LineTotal = EachCategory is what percent of Total Line Scrap

Create Table tblScrap
{
thaDate smalldatetime
Category varchar 15
lbs float
LineNum int
Shift int
}
Insert tblScrap Values ( ' 10/29/2003',PM , 0, 1 ,1)
Insert tblScrap Values ( ' 10/29/2004',DA , 0.66, 1 ,1)
Insert tblScrap Values ( ' 10/29/2005',DT , 0.5, 1 ,1)
Insert tblScrap Values ( ' 10/29/2006',Short , 0, 1 ,1)
Insert tblScrap Values ( ' 10/29/2007',Longs , 3.4, 1 ,1)
Insert tblScrap Values ( ' 10/29/2008',Bent , 1.48, 1 ,1)
Insert tblScrap Values ( ' 10/29/2009',NTA , 4.44, 1 ,1)
Insert tblScrap Values ( ' 10/29/2010',PIP , 0, 1 ,1)
Insert tblScrap Values ( ' 10/29/2011',Caps , 2.36, 1 ,1)
Insert tblScrap Values ( ' 10/29/2012',Paper , 5.26, 1 ,1)
Insert tblScrap Values ( ' 10/29/2013',NAPS , 0.66, 1 ,1)
Insert tblScrap Values ( ' 10/28/2003',PM , 0, 1 ,1)
Insert tblScrap Values ( ' 10/28/2004',DA , 0, 1 ,1)
Insert tblScrap Values ( ' 10/28/2005',DT , 0, 1 ,1)
Insert tblScrap Values ( ' 10/28/2006',Short , 0, 1 ,1)
Insert tblScrap Values ( ' 10/28/2007',Longs , 0, 1 ,1)
Insert tblScrap Values ( ' 10/28/2008',Bent , 0, 1 ,1)
Insert tblScrap Values ( ' 10/28/2009',NTA , 0, 1 ,1)
Insert tblScrap Values ( ' 10/28/2010',PIP , 0, 1 ,1)
Insert tblScrap Values ( ' 10/28/2011',Caps , 0, 1 ,1)
Insert tblScrap Values ( ' 10/28/2012',Paper , 0, 1 ,1)
Insert tblScrap Values ( ' 10/28/2013',NAPS , 0, 1 ,1)
Insert tblScrap Values ( ' 10/27/2003',PM , 0, 1 ,1)
Insert tblScrap Values ( ' 10/27/2004',DA , 0.44, 1 ,1)
Insert tblScrap Values ( ' 10/27/2005',DT , 0.44, 1 ,1)
Insert tblScrap Values ( ' 10/27/2006',Short , 0, 1 ,1)
Insert tblScrap Values ( ' 10/27/2007',Longs , 7.16, 1 ,1)
Insert tblScrap Values ( ' 10/27/2008',Bent , 1.84, 1 ,1)
Insert tblScrap Values ( ' 10/27/2009',NTA , 2.24, 1 ,1)
Insert tblScrap Values ( ' 10/27/2010',PIP , 0, 1 ,1)
Insert tblScrap Values ( ' 10/27/2011',Caps , 3.92, 1 ,1)
Insert tblScrap Values ( ' 10/27/2012',Paper , 7.86, 1 ,1)
Insert tblScrap Values ( ' 10/27/2013',NAPS , 1.76, 1 ,1)|||I'm not sure I fully understood what you are looking for.
This query groups by date and category with the percentage for each category in relation to the total of the day.

SELECT thaDate,
Category,
SUM(lbs) ScrapLbs,
(SELECT NULLIF(SUM(lbs), 0) FROM tblScrap WHERE thaDate = TS.thaDate) SumScrapLbs,
SUM(lbs) / (SELECT NULLIF(SUM(lbs), 0) WHERE thaDate = TS.thaDate) Percentage
FROM tblScrap TS
GROUP BY thaDate, Category

Hope this helps.

Cheers,
Robert|||clinel,

Still not sure what you mean. sum(Category)? Category is a character field. Also, what date ranges?
This should get you started:

select LineCatTotals.LineNum, LineCatTotals.Category, LineCatTotals.LineCatlbs/LineTotals.Linelbs LineCatPercent
from (select LineNum, sum(lbs) Linelbs from tblScrap group by LineNum) LineTotals
inner join (select LineNum, Category, sum(lbs) LineCatlbs from tblScrap group by LineNum, Category) LineCatTotals
on LineTotals.LineNum = LineCatTotals.LineNum

Add groupings by date or daterange if you want them.

blindman|||I'm sorry,
Category is the label for each category of scrap. I want to sum the lbs of scrap or each category would be a better term. Then I want to sum all lbs of scrap by line to get a line total and then divide the category total (for that line) by the line total to get the percent that each category contributes to the line total. Whew!

As far as date range goes, I will give the user the ability to give a beginning and ending date and I want to find the percentage for that date range.

My bad on the sum of category; I see now how I took a confusing thing and made it even more so.

Thanks for both the patience and help,
Lee|||Thanks to all,
It appears that what Blindman had sent me is what I needed. I was actually able to figure out where to set the critera for the date range. Now I just need to figure out what is going on because I have several reports that I think that this type query will fit the need.

Thanks again,
Lee|||If you'd like, post your final query and we can make sure you implemented the date-range criteria in the most efficient manner.

blindman|||As I am new to this and have had no formal and very little time to read very much, this is how I handled what you gave me.

I created a stored procedure (so I could set the critera for date range easily) and a I am allowing the user to set the begin and end and call it from an asp.

Here is how I handled the date range.

@.begdate smalldatetime, @.enddate smalldatetime
AS

select LineCatTotals.ProLine,
LineCatTotals.ScrapCat,
LineCatTotals.LineCatlbs/LineTotals.Linelbs LineCatPercent
from (select ProLine, sum(Scraplbs) Linelbs from clinel.otbl_SAAA_d_HSMainScrap WHERE ProDateTime BETWEEN @.begdate AND @.enddate group by ProLine) LineTotals
inner join (select ProLine, ScrapCat, sum(Scraplbs) LineCatlbs from clinel.otbl_SAAA_d_HSMainScrap WHERE ProDateTime BETWEEN @.begdate AND @.enddate group by ProLine, ScrapCat) LineCatTotals
on LineTotals.ProLine = LineCatTotals.ProLine Order by LineCatTotals.ProLine
GO

Now that you are looking over this, is it possible to select a total from another table and divide the Line total (scraplbs) by the production total from another table (Select Sum(Production) From tblProduction Where EntryDate Between @.begdate AND @.enddate) LineCatTotals.LineCatlbs/Sum(Production) ? Both tables could be linked on ProLine.|||Looks good to me.

Yes, you can add more subqueries to do additional calculations. It is generally more efficient to run your process as a single query, but if the query gets too confusing then consider breaking it up into separate statements that load temporary tables or table variables with summarized data. Then finish with a query that links these temporary tables to get the answer you need.

blindman