Group and Reduce - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Group and Reduce

Author

Topic

Girlnet
Yak Posting Veteran

77 Posts

Posted - 2008-08-01 : 15:05:03

How would you do this? I have a table like this . . .

Date EmpID Type Total
4/1 2678 BACKFL 72
4/1 2678 SCRAP 2
4/1 2678 DOWN 10

And I want it to be this . . .

Date EmpID BACKFL SCRAP DOWN
4/1 2678 72 2 10

Would this call for a pivot situation? I just can't get my head around it properly.

Girlnet
Yak Posting Veteran

77 Posts

Posted - 2008-08-01 : 15:13:12

Oh, and I only have SQL Server 2000

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-01 : 15:13:38

Then you should post in a SQL Server 2000 forum instead of one labeled "Transact-SQL (2005)". I'll move your thread.

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

Subscribe to my blog

Girlnet
Yak Posting Veteran

77 Posts

Posted - 2008-08-01 : 16:21:05

I think this may get me to where I want to go.

SELECT op_wkctr, op_part, op_date, op_wo_op, op_shift, op_emp,
Case when op_type='BACKFLSH' Then SUM(op_qty_comp) Else 0 End as BACK,
Case when op_type='SCRAP-I' Then SUM(op_qty_comp) Else 0 end AS SCRAP,
Case when op_type='DOWNTIME' Then SUM(op_act_run) Else 0 end AS DOWN
FROM dbo.QAD_op_hist OP1

GROUP BY OP1.op_wkctr, OP1.op_part, OP1.op_date, OP1.op_wo_op,
OP1.op_shift, OP1.op_emp, OP1.op_type

The issue now is I don't want it grouped by OP1.op_type. Any ideas?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-02 : 06:04:50

quote:
Originally posted by Girlnet

How would you do this? I have a table like this . . .

Date EmpID Type Total
4/1 2678 BACKFL 72
4/1 2678 SCRAP 2
4/1 2678 DOWN 10

And I want it to be this . . .

Date EmpID BACKFL SCRAP DOWN
4/1 2678 72 2 10

Would this call for a pivot situation? I just can't get my head around it properly.

SELECT date,
SUM(CASE WHEN Type='BACKFL' THEN Total ELSE 0 END) AS [BACKFL],
SUM(CASE WHEN Type='SCRAP' THEN Total ELSE 0 END) AS [SCRAP],
SUM(CASE WHEN Type='DOWN' THEN Total ELSE 0 END) AS [DOWN]
FROM Table
GROUP BY date

Girlnet
Yak Posting Veteran

77 Posts

Posted - 2008-08-02 : 10:53:46

Thanks so much for your reply. I certainly appreciate your effort. While it does run well, I get no sum on the op_qty_comp. When I add the sum(op_qty_comp ) in - I get "Cannot perform an aggregate function on an expression containing an aggregate or a subquery."

SELECT op_wkctr, op_part, op_date, op_wo_op, op_shift, op_emp,

SUM(CASE WHEN op_type='BACKFL' THEN op_qty_comp ELSE 0 END) AS [BACKFL],
SUM(CASE WHEN op_type='SCRAP' THEN op_qty_comp ELSE 0 END) AS [SCRAP],
SUM(CASE WHEN op_type='DOWN' THEN op_qty_comp ELSE 0 END) AS [DOWN]
FROM dbo.QAD_op_hist OP1
GROUP BY op_wkctr, op_part, op_date, op_wo_op, op_shift, op_emp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-02 : 12:37:21

quote:
Originally posted by Girlnet

Thanks so much for your reply. I certainly appreciate your effort. While it does run well, I get no sum on the op_qty_comp. When I add the sum(op_qty_comp ) in - I get "Cannot perform an aggregate function on an expression containing an aggregate or a subquery."

SELECT op_wkctr, op_part, op_date, op_wo_op, op_shift, op_emp,

SUM(CASE WHEN op_type='BACKFL' THEN op_qty_comp ELSE 0 END) AS [BACKFL],
SUM(CASE WHEN op_type='SCRAP' THEN op_qty_comp ELSE 0 END) AS [SCRAP],
SUM(CASE WHEN op_type='DOWN' THEN op_qty_comp ELSE 0 END) AS [DOWN]
FROM dbo.QAD_op_hist OP1
GROUP BY op_wkctr, op_part, op_date, op_wo_op, op_shift, op_emp

i guess op_qty_comp is a derived column. you can use aggregated functions on derived columns, can i have the full query used please?

Girlnet
Yak Posting Veteran

77 Posts

Posted - 2008-08-02 : 20:22:01

That is the full query, BACKFL, SCRAP, and DOWN all return 0. This is really driving me crazy! Thanks for your help.

Girlnet
Yak Posting Veteran

77 Posts

Posted - 2008-08-02 : 20:37:56

Maybe this will help . . .

create table op_hist (transdate datetime, op_qty_comp int, op_type varchar(25), emp_id int)

Insert into op_hist (transdate, op_qty_comp, op_type, emp_id) values (4/1/08, 10, 'backfl', 2729)
Insert into op_hist (transdate, op_qty_comp, op_type, emp_id) values (4/1/08, 10, 'backfl', 2729)
Insert into op_hist (transdate, op_qty_comp, op_type, emp_id) values (4/1/08, 30, 'backfl', 2729)
Insert into op_hist (transdate, op_qty_comp, op_type, emp_id) values (4/1/08, 10, 'scrap', 2729)
Insert into op_hist (transdate, op_qty_comp, op_type, emp_id) values (4/1/08, 10, 'scrap', 2729)
Insert into op_hist (transdate, op_qty_comp, op_type, emp_id) values (4/1/08, 10, 'down', 2729)
Insert into op_hist (transdate, op_qty_comp, op_type, emp_id) values (4/1/08, 10, 'down', 2729)
Insert into op_hist (transdate, op_qty_comp, op_type, emp_id) values (4/2/08, 10, 'backfl', 2730)
Insert into op_hist (transdate, op_qty_comp, op_type, emp_id) values (4/2/08, 30, 'backfl', 2730)
Insert into op_hist (transdate, op_qty_comp, op_type, emp_id) values (4/2/08, 10, 'scrap', 2730)
Insert into op_hist (transdate, op_qty_comp, op_type, emp_id) values (4/2/08, 10, 'scrap', 2730)
Insert into op_hist (transdate, op_qty_comp, op_type, emp_id) values (4/2/08, 10, 'down', 2730)
Insert into op_hist (transdate, op_qty_comp, op_type, emp_id) values (4/2/08, 10, 'down', 2730)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-03 : 13:44:42

quote:
Originally posted by Girlnet

Maybe this will help . . .

create table op_hist (transdate datetime, op_qty_comp int, op_type varchar(25), emp_id int)

Insert into op_hist (transdate, op_qty_comp, op_type, emp_id) values (4/1/08, 10, 'backfl', 2729)
Insert into op_hist (transdate, op_qty_comp, op_type, emp_id) values (4/1/08, 10, 'backfl', 2729)
Insert into op_hist (transdate, op_qty_comp, op_type, emp_id) values (4/1/08, 30, 'backfl', 2729)
Insert into op_hist (transdate, op_qty_comp, op_type, emp_id) values (4/1/08, 10, 'scrap', 2729)
Insert into op_hist (transdate, op_qty_comp, op_type, emp_id) values (4/1/08, 10, 'scrap', 2729)
Insert into op_hist (transdate, op_qty_comp, op_type, emp_id) values (4/1/08, 10, 'down', 2729)
Insert into op_hist (transdate, op_qty_comp, op_type, emp_id) values (4/1/08, 10, 'down', 2729)
Insert into op_hist (transdate, op_qty_comp, op_type, emp_id) values (4/2/08, 10, 'backfl', 2730)
Insert into op_hist (transdate, op_qty_comp, op_type, emp_id) values (4/2/08, 30, 'backfl', 2730)
Insert into op_hist (transdate, op_qty_comp, op_type, emp_id) values (4/2/08, 10, 'scrap', 2730)
Insert into op_hist (transdate, op_qty_comp, op_type, emp_id) values (4/2/08, 10, 'scrap', 2730)
Insert into op_hist (transdate, op_qty_comp, op_type, emp_id) values (4/2/08, 10, 'down', 2730)
Insert into op_hist (transdate, op_qty_comp, op_type, emp_id) values (4/2/08, 10, 'down', 2730)

no probelms here op_qty_comp seems to be a field in table then you will be able to use earlier query. b/w are you trying to use SUM inside SUM?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources