Need a Little Help with Group by Sum

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need a Little Help with Group by Sum

Author

Topic

chris_work_2005
Starting Member

2 Posts

Posted - 2007-12-27 : 13:47:26

Newb here, please let me know if I'm not following the proper forum rules. I tried to present it properly.

Here's a simplified version of the table I'm working with
TABLE 1:

sku_id_______time______slot
'blank'_________4_________111
'blank'_________2_________222
aaaa__________1_________111
bbbb__________6_________222
cccc__________8_________333
cccc__________2_________333
'blank'_________7_________333
dddd__________10________444
'blank'_________8_________444
aaaa__________4_________111

(the 'blank' represents a lack of any value, or is null

I've tried various ways to achieve the following: sum the time, group by slot, and include a unique value from the sku_id column that is not blank/null. I'm trying to get the following table as a result:

sku_id_______time______slot
aaaa__________9_________111
bbbb__________8_________222
cccc__________17________333
dddd__________18________444

For this table, there is a unique relationship between sku_id and slot, except sometimes the slot is listed with time, but without a sku_id. I want to sum all of the time for each slot, and have it's corresponding sku_id listed, even if the sku_id was not listed for a row that had time listed.

Clear as mud?

I'm currently at:
SELECT SUM(time), slot FROM Table
GROUP BY slot

Thanks in advance,
Chris

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-27 : 13:56:19

Untested:


SELECT t.sku_id, d.time, d.slot
FROM Table t
INNER JOIN
(
	SELECT SUM(time) AS time, slot 
	FROM Table
	GROUP BY slot
) d
ON t.slot = d.slot
WHERE t.sku_id <> 'blank'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-27 : 14:39:20

quote:
Originally posted by tkizer

Untested:

SELECT DISTINCT t.sku_id, d.time, d.slot
FROM Table t
INNER JOIN
(
SELECT SUM(time) AS time, slot
FROM Table
GROUP BY slot
) d
ON t.slot = d.slot
WHERE t.sku_id <> 'blank'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

I think you should give a distinct else the result will repeat for 111,333,...( having more than 1 records with non blank values)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-12-27 : 14:39:24

What's wrong with:

SELECT sku_id, SUM(time), slot
FROM Table
where sku_id is not null
GROUP BY sku_id, slot

??

- Jeff
http://weblogs.sqlteam.com/JeffS

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-27 : 14:44:20

I thought blanks represented his null values and that null was not used.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

chris_work_2005
Starting Member

2 Posts

Posted - 2007-12-27 : 17:17:00

I appreciate the responses. However, translating the suggestions into results with my original query has been tough though due to my lack of experience. I should have provided a little more information.

Here is the original Query that I'm using to get the initial "table" that I referred to. This is a production server, so I do not want to write a table or create a view with this query. The suggestions look like a sub-query, so I'm trying to figure out how to refer to the table and columns that are produced by my mainquery, in the sub-query:

MAINQUERY

SELECT
e_aud_log.sku_id as 'SKU', e_aud_log.tot_time as 'Time', e_aud_log.tot_units as 'Units', e_aud_log.curr_slot as 'Slot'
FROM
e_aud_log (nolock) inner join view_engine_type on (e_aud_log.module_type = view_engine_type.misc_flags)
inner join e_evnt_smry_hdr on ((e_aud_log.work_assigt_nbr = e_evnt_smry_hdr.tran_nbr) and (e_aud_log.whse = e_evnt_smry_hdr.whse))
inner join e_act on ((e_aud_log.whse = e_act.whse) and (e_aud_log.act_name = e_act.name))
WHERE
e_aud_log.whse = '018'
AND
e_aud_log.act_name = 'ofrkpk'
AND
e_evnt_smry_hdr.login_user_id = 'adecker'
AND
e_aud_log.ta_multiplier > '0'
AND
view_engine_type.code_desc = 'Labor Management Engine'
AND
e_aud_log.create_date_time > '2007-12-17'
AND
e_aud_log.create_date_time < '2007-12-18'

Produces this Table:
SKU Time Units Slot
5127825 0.0011 11 314312
_______ 1.101 11 314312
5127824 0.0005 5 314212
5127824 1.101 5 314212
_______ 0.81 5 314212
5127823 0.001 10 314411
_______ 1.62 10 314411
5127822 0.0003 3 314011
5127822 0.486 3 314011
5127789 0.001 10 151911
_______ 0.0003 3 151911
_______ 0.0003 3 151911
5127788 0.0014 14 152912
5127787 0.0004 4 151711

So, should I be able to squeeze my entire query in parentheses into the script that you have written? (with correct table and column syntax)

SELECT DISTINCT t.sku_id, d.time, d.slot
FROM (MAINQUERY) t
INNER JOIN
(
SELECT SUM(time) AS time, slot
FROM (MAINQUERY)
GROUP BY slot
) d
ON t.slot = d.slot
WHERE t.sku_id <> 'blank'

Objective
Sum the time for each Slot (regardless of whether SKU is populated in original query), and then populate the SKU column for each row with the unique/distinct value associated with that Slot. For example, location 314411 would have time of 1.621 (1.62+0.001) and SKU 5127823.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-28 : 01:18:51

SELECT DISTINCT t.SKU, d.time, t.Slot
FROM (SELECT
e_aud_log.sku_id as 'SKU', e_aud_log.tot_time as 'Time', e_aud_log.tot_units as 'Units', e_aud_log.curr_slot as 'Slot'
FROM
e_aud_log (nolock) inner join view_engine_type on (e_aud_log.module_type = view_engine_type.misc_flags)
inner join e_evnt_smry_hdr on ((e_aud_log.work_assigt_nbr = e_evnt_smry_hdr.tran_nbr) and (e_aud_log.whse = e_evnt_smry_hdr.whse))
inner join e_act on ((e_aud_log.whse = e_act.whse) and (e_aud_log.act_name = e_act.name))
WHERE
e_aud_log.whse = '018'
AND
e_aud_log.act_name = 'ofrkpk'
AND
e_evnt_smry_hdr.login_user_id = 'adecker'
AND
e_aud_log.ta_multiplier > '0'
AND
view_engine_type.code_desc = 'Labor Management Engine'
AND
e_aud_log.create_date_time > '2007-12-17'
AND
e_aud_log.create_date_time < '2007-12-18') t
INNER JOIN
(
SELECT SUM(s.Time) AS time, s.Slot
FROM (SELECT e_aud_log.tot_time as 'Time', e_aud_log.curr_slot as 'Slot'
FROM
e_aud_log (nolock) inner join view_engine_type on (e_aud_log.module_type = view_engine_type.misc_flags)
inner join e_evnt_smry_hdr on ((e_aud_log.work_assigt_nbr = e_evnt_smry_hdr.tran_nbr) and (e_aud_log.whse = e_evnt_smry_hdr.whse))
inner join e_act on ((e_aud_log.whse = e_act.whse) and (e_aud_log.act_name = e_act.name))
WHERE
e_aud_log.whse = '018'
AND
e_aud_log.act_name = 'ofrkpk'
AND
e_evnt_smry_hdr.login_user_id = 'adecker'
AND
e_aud_log.ta_multiplier > '0'
AND
view_engine_type.code_desc = 'Labor Management Engine'
AND
e_aud_log.create_date_time > '2007-12-17'
AND
e_aud_log.create_date_time < '2007-12-18')s
GROUP BY s.Slot) d
ON t.Slot = d.Slot
WHERE t.SKU <> 'blank'

Or you can define a CTE for main query and use it for both occasions. This will prevent you from repeating query two times

;
With SlotTime_CTE (SKU,Time,Units,Slot)AS
(
SELECT
e_aud_log.sku_id as 'SKU',
e_aud_log.tot_time as 'Time',
e_aud_log.tot_units as 'Units',
e_aud_log.curr_slot as 'Slot'
FROM
e_aud_log (nolock)
inner join view_engine_type
on (e_aud_log.module_type = view_engine_type.misc_flags)
inner join e_evnt_smry_hdr
on ((e_aud_log.work_assigt_nbr = e_evnt_smry_hdr.tran_nbr)
and (e_aud_log.whse = e_evnt_smry_hdr.whse))
inner join e_act
on ((e_aud_log.whse = e_act.whse)
and (e_aud_log.act_name = e_act.name))
WHERE e_aud_log.whse = '018'
AND e_aud_log.act_name = 'ofrkpk'
AND e_evnt_smry_hdr.login_user_id = 'adecker'
AND e_aud_log.ta_multiplier > '0'
AND view_engine_type.code_desc = 'Labor Management Engine'
AND e_aud_log.create_date_time > '2007-12-17'
AND e_aud_log.create_date_time < '2007-12-18'
)

SELECT DISTINCT t.SKU, d.Time, t.Slot
FROM SlotTime_CTE t
INNER JOIN
(
SELECT SUM(s.Time) AS Time, s.Slot
FROM SlotTime_CTE s
GROUP BY s.Slot
) d
ON t.Slot = d.Slot
WHERE t.SKU <> 'blank'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources