summaring - 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)

summaring

Author

Topic

u2p_inst
Yak Posting Veteran

78 Posts

Posted - 2002-12-26 : 23:24:25

i have table like this

A B C D E F
-----------------------------------
1 4
2 5
3 8
1 9
1 2
2 3
3 1

HOW TO CREATE SQL QUERY WHICH THE RESULT OF COLUMN C IS
(SUMMERY GROUP BY COLUMN A * 100) AND COLUMN D IS
(COLUMN C/4)

oh

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2002-12-27 : 05:27:57

I am not quite sure about the question, do you want this to appear only in the SELECT query, or do you want to update columns C & D to the mentioned values? If you want to select this should do it...

SELECT A, SUM(B) AS Summary, SUM(B)*100 AS C, (SUM(B)*100)/4 AS D FROM TableName

I think you should be able to run an UPDATE with the same query and a self-join

HTH

OS
==============================================================
I started out with nothing. Still have most of it left.

u2p_inst
Yak Posting Veteran

78 Posts

Posted - 2002-12-27 : 05:59:45

yup, i want "select" only, but the "SUM" sintaks is grouping by column A.
is it posible??

oh

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-27 : 09:25:36

CREATE TABLE #Test (A int, B int)

INSERT INTO #Test (A,B) VALUES (1,4)
INSERT INTO #Test (A,B) VALUES (2,5)
INSERT INTO #Test (A,B) VALUES (3,8)
INSERT INTO #Test (A,B) VALUES (1,9)
INSERT INTO #Test (A,B) VALUES (1,2)
INSERT INTO #Test (A,B) VALUES (2,3)
INSERT INTO #Test (A,B) VALUES (3,1)

SELECT A.A A, A.B B, SUMOFB*100 C, (SUMOFB*100)/4 D
FROM #Test A INNER JOIN
(
SELECT A, SUM(B) SUMOFB
FROM #TEST
GROUP BY A
) B ON A.A = B.A

DROP TABLE #Test

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2002-12-27 : 13:31:51

quote:

yup, i want "select" only, but the "SUM" sintaks is grouping by column A.
is it posible??

oh

Is this what you intend?

SELECT A, SUM(B) AS Summary, SUM(B)*100 AS C, (SUM(B)*100)/4 AS D FROM TableName GROUP BY A

Valter:
Will your query return a different result from the one above? you used a derived table, does it make the query faster?

OS

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-27 : 19:08:28

My Query Returns.


A       B        C       D
1	9	1500	375
1	2	1500	375
1	4	1500	375
2	5	800	200
2	3	800	200
3	1	900	225
3	8	900	225

Your's Returns.


A       B       C        D
1	15	1500	375
2	8	800	200
3	9	900	225

I interpreted the question to mean that the resultset needs to include
all the subrecords with the totals based on grouping by colA.

You said it yourself.

quote:

I think you should be able to run an UPDATE with the same query and a self-join

That's the same idea except my query does it in a select statement.

Edited by - ValterBorges on 12/27/2002 19:12:22

Edited by - ValterBorges on 12/27/2002 19:14:05

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2002-12-28 : 02:05:06

uh...that

, i see, so that the user gets the summary and still gets the details...have your cake and eat it too!
thanx

OS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources