Simple Sub-Query Group By - 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)

Simple Sub-Query Group By

Author

Topic

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2003-09-04 : 13:23:09

Here's the data
FHSHAN FHSHPN FHNAMT FHWGTS
100 502 1 100
100 502 2 100
100 502 3 100
100 503 1 200
100 503 2 200

I want to group by FHSHAN, totaling FHNAMT and FHWGTS based on FHSHPN. So, I wanty the above result to be...

FHSHAN FHSHPN FHNAMT FHWGTS
100 502 9 300

When I do this...
SELECT T1.fhshan,SUM(T1.fhnamt),
(SELECT SUM(T2.fhwgts) FROM testdta.F4981 T2
WHERE T2.fhshpn = T1.fhshpn) AS Weight
FROM testdta.F4981 T1 GROUP BY T1.fhshan

I get an error...
Column 'T1.FHSHPN' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I know I am missing something simple here. Sorry for my brain cramp, and thanks in advance.

Ken

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-04 : 13:34:44

quote:
Originally posted by Ken Blum

Here's the data
FHSHAN FHSHPN FHNAMT FHWGTS
100 502 1 100
100 502 2 100
100 502 3 100
100 503 1 200
100 503 2 200

I want to group by FHSHAN, totaling FHNAMT and FHWGTS based on FHSHPN. So, I wanty the above result to be...

FHSHAN FHSHPN FHNAMT FHWGTS
100 502 9 300

What happened to 503? Why are you adding all of FHNAMT, but not FHWGTS?

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-04 : 13:36:35

what happens to the 503 in your sample data?
do you want the second column to be the MIN of FHSPN per FHSHAN?

(by the way -- SQL server allows for column names > 8 characters

)

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-04 : 13:47:31

Don't you really want...


USE Northwind
GO

CREATE TABLE F4981 (FHSHAN int, FHSHPN int, FHNAMT int, FHWGTS int)
GO

INSERT INTO F4981 (FHSHAN, FHSHPN, FHNAMT, FHWGTS)
SELECT 100, 502, 1, 100 UNION ALL
SELECT 100, 502, 2, 100 UNION ALL
SELECT 100, 502, 3, 100 UNION ALL
SELECT 100, 503, 1, 200 UNION ALL
SELECT 100, 503, 2, 200
GO


  SELECT  FHSHAN
	, FHSHPN
	, SUM(fhnamt)
	, SUM(fhwgts)
    FROM  F4981
GROUP BY  FHSHAN
	, FHSHPN
GO

DROP TABLE F4981
GO

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2003-09-04 : 13:49:17

Sorry, I want the result to be:
FHSHAN FHNAMT FHWGTS
100 9 300

I want the total of FHNAMT based on FHSHAN
I want the total of FHWGTS based on FHSHAN and SHSHPN

BTW - I did not design this table. It's from a JD Edwards ERP system.

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2003-09-04 : 13:55:29

quote:

Originally posted by X002548

Don't you really want...


USE Northwind
GO

CREATE TABLE F4981 (FHSHAN int, FHSHPN int, FHNAMT int, FHWGTS int)
GO

INSERT INTO F4981 (FHSHAN, FHSHPN, FHNAMT, FHWGTS)
SELECT 100, 502, 1, 100 UNION ALL
SELECT 100, 502, 2, 100 UNION ALL
SELECT 100, 502, 3, 100 UNION ALL
SELECT 100, 503, 1, 200 UNION ALL
SELECT 100, 503, 2, 200
GO


  SELECT  FHSHAN
	, FHSHPN
	, SUM(fhnamt)
	, SUM(fhwgts)
    FROM  F4981
GROUP BY  FHSHAN
	, FHSHPN
GO

DROP TABLE F4981
GO

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

No Brett, that would give me 2 rows, and total fhwgts. I only want the total of fhwgts based on each fhshpn (100 + 200).

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-04 : 14:07:32

you need to do two seperate levels of grouping:

select fhshan, sum(FHNAmt) as FHNAmt, SUM(FHWGTS) as FHWGTS
FROM
(
select fhshan, sum(fhnamt) as FHNAmT, FHWGTS
from tbl
group by fhshan, fhwgts
) A
GROUP BY FHSHAN

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-04 : 14:37:11

Ok...I think I've got it...the table sucks...

Your data in the last column is duplicated right?

The how about:


    SELECT  a.FHSHAN
	  , Sum_FHNAMT
	  , Sum_FHWGTS
      FROM  
	(   SELECT  FHSHAN, SUM(FHNAMT) AS Sum_FHNAMT
	      FROM  F4981
	  GROUP BY  FHSHAN) as a
INNER JOIN
	(   SELECT FHSHAN, SUM(FHWGTS) AS Sum_FHWGTS 
	      FROM (SELECT DISTINCT FHSHAN, FHWGTS 
			     FROM F4981) as b
	  GROUP BY FHSHAN) as c
	ON a.FHSHAN = c.FHSHAN
GO

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

EDIT: Smoked me by a 1/2 hour Jeff...that's what I get for not refreshing...damn mainframe...

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2003-09-04 : 15:10:15

Yup, that's it with a small modification on the select distinct...
SELECT a.FHSHAN, Sum_FHNAMT, Sum_FHWGTS
FROM
( SELECT FHSHAN, SUM(FHNAMT) AS Sum_FHNAMT
FROM Testdta.F4981
GROUP BY FHSHAN) as a
INNER JOIN
( SELECT FHSHAN, SUM(FHWGTS) AS Sum_FHWGTS
FROM (SELECT DISTINCT FHSHAN, FHSHPN, FHWGTS
FROM Testdta.F4981) as b
GROUP BY FHSHAN) as c
ON a.FHSHAN = c.FHSHAN

Thanks a bunch Brett!

BTW, have you ever seen a table in any ERP system that doesn't suck? I'm pretty sure that ERP stands for "Everyone Relies on Programming" :)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-04 : 15:17:18

Why do you guys have the JOIN in there? did you see my solution?

there's no need for the self join .... look closely at my solution and try it out.

no point in overcomplicating.

- Jeff

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2003-09-04 : 15:32:01

quote:
Originally posted by jsmith8858

Why do you guys have the JOIN in there? did you see my solution?

there's no need for the self join .... look closely at my solution and try it out.

no point in overcomplicating.

- Jeff

But it's an ERP system, don't we have to overcomplicate?

Thanks Jeff, that works also and was actually in the direction I was taking to begin with.

Actually, now that I look at it it needed one change...

SELECT FHSHAN, sum(FHnAmt) as FHnAmt, SUM(FHwGts) as FHwGts
FROM
(
SELECT FHSHAN, sum(FHnAmt) as FHnAmt, FHWGTS, FHSHPN
FROM proddta.f4981
GROUP BY FHSHAN, FHSHPN, FHWGTS
) A
GROUP BY FHSHAN

cuz we could have two of the same weights (FHwGts) for separate shipment numbers (FHSHPN)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-04 : 15:40:05