Sum count by day then return max

[WaveOut]

Hello,
I have the following data:

Craftname day_of_week count
Craft 1 Monday 2
Craft 2 Monday 8
Craft 3 Monday 3
Craft 4 Monday 5
Craft 1 Tuesday 1
Craft 2 Tuesday 4
Craft 3 Tuesday 1
Craft 4 Tuesday 3


I would like to sum the above data by day then return the max count. For example

Monday = 2 + 8 + 3 + 5 = 18
Tuesday = 1 + 4 + 1 + 3 = 9

I need to return the number 18 or which ever day has the largest summed count. How can I do this in SQL?

Dave

 

[r937]

select top 1
       Craftname        
     , day_of_week        
     , sum(count) as sum_count
  from daTable
group
    by Craftname        
     , day_of_week  
order
    by 3 desc

r937.com | rudy.ca

 

[WaveOut]

Thanks for the reply. I think your code is very close to what I need, however I will need to be stepping through many weeks and I need the maximum for each week.

Dave

 

[r937]

then perhaps you should have mentioned that?

select Craftname        
     , daWeek
     , day_of_week        
     , sum(count) as sum_count
  from daTable as T
 where sum(count) = 
       ( select max(subtotal)
           from (
                select sum(count) as subtotal
                  from daTable
                 where Craftname = T.Craftname
                   and daWeek    = T.daWeek
                group
                    by day_of_week
                ) as d )
group
    by Craftname        
     , daWeek
     , day_of_week

caution: untested

r937.com | rudy.ca

 

[WaveOut]

Thanks again for the help ... Here is the actual code I have so far ( I know it's alot diffrent from my initial post but I was trying to keep if simple)

SELECT top 100 percent end_date, craft_category, craft_name, day_of_week, day_count, night_count,
max(subtotal) AS max_count_week
FROM dbo.craft2 inner join dbo.WeeklyData ON dbo.Craft2.weeklyUid = dbo.WeeklyData.weeklyUID,
(SELECT SUM(day_count)+sum(night_count) AS subtotal
FROM craft2 AS T INNER JOIN
dbo.WeeklyData ON t.weeklyUID = dbo.WeeklyData.weeklyUID
where end_date between '02-11-2007' and '02/19/2007' and day_of_week = day_of_week
GROUP BY day_of_week,end_date) DERIVEDTBL
where day_count > '0' and end_date between '02-10-2007' and '02/19/2007' or night_count > '0' and end_date between '02-10-2007' and '02/19/2007'
group by end_date, day_of_week, craft_category, day_count, night_count, craft_name
order by end_date, day_of_week, craft_category


-------------------------

I get the following results:

end_date craft_category craft_name day_of_week day_count night_count max_count_week
2007-02-11 00:00:00.000 Boilermaker Boilermaker Foremen 1-Monday 2 NULL 106
2007-02-11 00:00:00.000 Boilermaker Boilermaker Apprentice Welders 7th Period 1-Monday 3 NULL 106
2007-02-11 00:00:00.000 Boilermaker Boilermaker Apprentice Welders 5th Period 1-Monday 5 NULL 106
2007-02-11 00:00:00.000 Boilermaker Boilermaker JM Mechanics 1-Monday 8 NULL 106
2007-02-11 00:00:00.000 Boilermaker Boilermaker Foremen 2-Tuesday 1 3 106
2007-02-11 00:00:00.000 Boilermaker Boilermaker Apprentice Welders 7th Period 2-Tuesday 1 10 106
2007-02-11 00:00:00.000 Boilermaker Boilermaker Apprentice Welders 5th Period 2-Tuesday 3 10 106
2007-02-11 00:00:00.000 Boilermaker Boilermaker JM Mechanics 2-Tuesday 4 10 106
2007-02-18 00:00:00.000 Boilermaker Boilermaker Foremen 1-Monday 2 1 106
2007-02-18 00:00:00.000 Boilermaker Boilermaker JM Mechanics 1-Monday 20 1 106
2007-02-18 00:00:00.000 Boilermaker Boilermaker Apprentice Welders 5th Period 1-Monday 40 1 106
2007-02-18 00:00:00.000 Boilermaker Boilermaker Apprentice Welders 7th Period 1-Monday 40 1 106

----------------
I know that is a little jumbled ... I could not get it to format correctly. What I want to point your attention to is the value 106. That is indeed the max number for the range that I'm looking at ... but I need the max count for each week (notice there are 2 weeks in this example) ... so the first week (2007-02-11) max will be 42 and the 2nd week (2007-02-18) max will be 106. How can I make it sum each week individually.

Dave

 

[r937]

I know it's alot diffrent from my initial post but I was trying to keep if simple"



r937.com | rudy.ca

 

[r937]

your DERIVEDTBL contains one row for every day_of_week, end_date combination

all of these rows are matched with every row produced by the INNER JOIN in the outer query, through the "comma-style join" with the INNER JOIN, because there is no join condition for the derived table

also, a condition like this --

and day_of_week = day_of_week

will always be true

would you please fix those issues before we try again?

r937.com | rudy.ca