How to sum group by column name? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

How to sum group by column name?

Author

Topic

Vaishu
Posting Yak Master

178 Posts

Posted - 2008-01-11 : 06:20:04

Hi

I have table and the column as ID, orderID, Uprice, weight ...upto 25 column.

ID, orderID, Uprice, weight
1 123 5.50 100
2 327 8.90 350
3 625 5.70 200
4 123 11.80 345

How do I sum the total of UPRICE group by orderID and select rest of the columns as well. ( SUM only the Uprice)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-11 : 06:35:13

Try :-

SELECT t1.ID, t1.orderID, t2.TotUprice, t1.weight
FROM Table t1
INNER JOIN (SELECT orderID,SUM(Uprice) AS 'TotUprice'
            FROM Table
            GROUP BY orderID)t2
ON t2.orderId=t1.orderID

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-11 : 07:07:38

quote:

Originally posted by visakh16

Try :-

SELECT t1.ID, t1.orderID, t2.TotUprice, t1.weight
FROM Table t1
INNER JOIN (SELECT orderID,SUM(Uprice) AS 'TotUprice'
            FROM Table
            GROUP BY orderID)t2
ON t2.orderId=t1.orderID

Except that you should avoid having single or double quotes around alias names.

Madhivanan

Failing to plan is Planning to fail

Vaishu
Posting Yak Master

178 Posts

Posted - 2008-01-11 : 09:29:41

Thank you very much

quote:

Originally posted by visakh16

Try :-

SELECT t1.ID, t1.orderID, t2.TotUprice, t1.weight
FROM Table t1
INNER JOIN (SELECT orderID,SUM(Uprice) AS 'TotUprice'
            FROM Table
            GROUP BY orderID)t2
ON t2.orderId=t1.orderID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-12 : 01:52:06

quote:
Originally posted by madhivanan

quote:
Originally posted by visakh16

Try :-
SELECT t1.ID, t1.orderID, t2.TotUprice, t1.weight
FROM Table t1
INNER JOIN (SELECT orderID,SUM(Uprice) AS 'TotUprice'
FROM Table
GROUP BY orderID)t2
ON t2.orderId=t1.orderID

Except that you should avoid having single or double quotes around alias names.

Madhivanan

Failing to plan is Planning to fail

Sorry madhi, i always had the habit of doing that. Just being curious, may i know the reason behind insisting on this?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-12 : 06:17:52

quote:
Originally posted by visakh16

quote:
Originally posted by madhivanan

quote:
Originally posted by visakh16

Try :-
SELECT t1.ID, t1.orderID, t2.TotUprice, t1.weight
FROM Table t1
INNER JOIN (SELECT orderID,SUM(Uprice) AS 'TotUprice'
FROM Table
GROUP BY orderID)t2
ON t2.orderId=t1.orderID

Except that you should avoid having single or double quotes around alias names.

Madhivanan

Failing to plan is Planning to fail

Sorry madhi, i always had the habit of doing that. Just being curious, may i know the reason behind insisting on this?

1 It wont work in all DBMSs
2 It looks like a string value than alias name

See What happens if some newbies try to use quoted alias name in a derived table

select 'number' from
(
select 1 as 'number'
) as t

Now you remove the single quote and run

select number from
(
select 1 as number
) as t

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-12 : 06:36:37

quote:
Originally posted by madhivanan

quote:
Originally posted by visakh16

quote:
Originally posted by madhivanan

quote:
Originally posted by visakh16

Try :-
SELECT t1.ID, t1.orderID, t2.TotUprice, t1.weight
FROM Table t1
INNER JOIN (SELECT orderID,SUM(Uprice) AS 'TotUprice'
FROM Table
GROUP BY orderID)t2
ON t2.orderId=t1.orderID

Except that you should avoid having single or double quotes around alias names.

Madhivanan

Failing to plan is Planning to fail

Sorry madhi, i always had the habit of doing that. Just being curious, may i know the reason behind insisting on this?

1 It wont work in all DBMSs
2 It looks like a string value than alias name

See What happens if some newbies try to use quoted alias name in a derived table

select 'number' from
(
select 1 as 'number'
) as t

Now you remove the single quote and run

select number from
(
select 1 as number
) as t

Madhivanan

Failing to plan is Planning to fail

That explains it. Thanks madhi. Actually it is the influence of coding standards followed in our company that's causing it

. Will watch this out in future. Cheers.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-12 : 07:22:02

<<
That explains it. Thanks madhi. Actually it is the influence of coding standards followed in our company that's causing it . Will watch this out in future. Cheers.
>>

You are welcome

You seem to have good knowledge in sql and actively helping others
Keep it up

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-12 : 09:42:10

quote:
Originally posted by madhivanan

<<
That explains it. Thanks madhi. Actually it is the influence of coding standards followed in our company that's causing it . Will watch this out in future. Cheers.
>>

You are welcome
You seem to have good knowledge in sql and actively helping others
Keep it up

Madhivanan

Failing to plan is Planning to fail

Thanks madhi. Honestly speaking,its people like you who are the real inspiration for me.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources