sum without 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)

sum without group by

Author

Topic

wsqlw
Starting Member

8 Posts

Posted - 2008-10-01 : 08:19:01

I have 3 Tables:

Table1 looks like this:

ID Votes Name
1 20000 Anna
2 15000 Marc
3 13000 Nick

Table2 looks like this:

ID Votes
1 12000
2 14000
3 10000

Table3 looks like this:

ID Votes
1 14000
2 12500
3 16000

I have joined them to get a virtual table that looks like this:

ID Name VotesTable1 VotesTable2 VotesTable3 Total
1 Anna 20000 12000 14000 46000
2 Marc 15000 14000 12500 41500
3 Nick 13000 10000 16000 39000

select G.ID, G.Name, G.Table1, G.Table2, G.Table3, sum(VotesTable1 + VotesTable2 + VotesTable3)Total
from
(select c.ID, c.Name, c.Table1, c.Table2, z.Votes as 'VotesTable3'
from
(select A.ID, A.Name, A.Votes as 'VotesTable1', B.Votes as'VotesTable2'
from (select *
from Table1)A
left join
(select *
from Table2)B
on A.ID = B.ID)C

left join

(select *
from Table3)Z

on C.ID = Z.ID)G
group by G.ID, G.Name, G.Table1, G.Table2, G.Table3

This works fine but now I need to write a querry that will return the same as the one above but when I sum the votes I need to sum them without the group by clause (group by G.ID, G.Name, G.Table1, G.Table2, G.Table3). How can I do that?
Any idea?
Thanks in advance...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-01 : 08:27:09

Sum which columns?you mean all Vote values?

wsqlw
Starting Member

8 Posts

Posted - 2008-10-01 : 08:37:07

Yes, I need to sum votes from all Tables for each ID. For example, if the ID is 1: sum votes from Table1 for that ID (20000), sum votes from Table2(12000) and from Table3(14000) for that same person with an ID:1 and place the sum in column Total. In fact, sum without group by would return the same table that I got with my query, but now I just have to do the sum without having to group by anything.
Thanks in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-01 : 08:52:04

Try WITH ROLLUP

SELECT		d.ID,
		t1.Name,
		SUM(d.v1) AS Votes1,
		SUM(d.v2) AS Votes2,
		SUM(d.v3) AS Votes3,
		SUM(d.v1 + d.v2 + d.v3) AS Total
FROM		(
			SELECT	ID,
				Votes AS v1,
				0 AS v2,
				0 AS v3
			FROM	Table1

			UNION ALL

			SELECT	ID,
				0,
				Votes,
				0
			FROM	Table2

			UNION ALL

			SELECT	ID,
				0,
				0,
				Votes
			FROM	Table3
		) AS d
LEFT JOIN	Table1 AS t1 ON t1.ID = d.ID
GROUP BY	d.ID,
		t1.Name
WITH ROLLUP

E 12°55'05.63"
N 56°04'39.26"

CodesMyBusiness
Starting Member

9 Posts

Posted - 2008-10-01 : 08:57:22

I'm having trouble seeing what your trying to do in your query.

Why can't you use a GROUP BY? SUMing is going to be a lot easier.

SELECT
  a.ID,
  SUM(a.Votes),
  SUM(b.Votes),
  SUM(c.Votes),
  SUM(a.Votes)+SUM(b.Votes)+SUM(c.Votes) as "Total"
FROM Table1 a
LEFT JOIN Table2 b
  ON b.ID = a.ID
LEFT JOIN Table3 c
  ON c.ID = a.ID
GROUP BY a.ID

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-01 : 09:02:28

Rookie mistake. Learn your joins.

Written LEFT JOINs that behave like CROSS JOIN due to multiple Fks.
If you do not trust me, look at this example

DECLARE	@a TABLE (pk INT)
DECLARE	@b TABLE (fk INT, i INT)
DECLARE	@c TABLE (fk INT, j INT)

INSERT	@a
SELECT	1 UNION ALL
SELECT	2 UNION ALL
SELECT	3

INSERT	@b
SELECT	1, 1 UNION ALL
SELECT	1, 3 UNION ALL
SELECT	2, 4 UNION ALL
SELECT	2, 8 UNION ALL
SELECT	2, 10 UNION ALL
SELECT	3, 1

INSERT	@c
SELECT	1, 11 UNION ALL
SELECT	1, 13 UNION ALL
SELECT	2, 14 UNION ALL
SELECT	2, 18 UNION ALL
SELECT	2, 60 UNION ALL
SELECT	3, 11

select		a.pk,
		SUM(b.i) AS SumAct,
		SUM(c.j) AS SumDebt
from		@a as a
left join	@b as b on b.fk = a.pk
left join	@c as c on c.fk = a.pk
group by	a.pk

I think sum 276 is to high! Should only be 92.
The reason for this is that there also is 3 records in @b table that share same fk. 92 times 3 is 276!!![/code]

E 12°55'05.63"
N 56°04'39.26"

wsqlw
Starting Member

8 Posts

Posted - 2008-10-01 : 09:22:40

Well I think you all are right. I tried your sugestions and they all work. It really helped me to optimize my query, make it run faster. I'll talk to my teacher and let you know if he's ok with it.
Thank's!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-01 : 09:29:10

[code]DECLARE @a TABLE (pk INT)
DECLARE @b TABLE (fk INT, i INT)
DECLARE @c TABLE (fk INT, j INT)

INSERT @a
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3

INSERT @b
SELECT 1, 1 UNION ALL
SELECT 1, 3 UNION ALL
SELECT 2, 4 UNION ALL
SELECT 2, 8 UNION ALL
SELECT 2, 10 UNION ALL
SELECT 3, 1

INSERT @c
SELECT 1, 11 UNION ALL
SELECT 1, 13 UNION ALL
SELECT 2, 14 UNION ALL
SELECT 2, 18 UNION ALL
SELECT 2, 60 UNION ALL
SELECT 3, 11

-- Wrong way
SELECT a.pk,
SUM(b.i) AS SumAct,
SUM(c.j) AS SumDebt
FROM @a AS a
LEFT JOIN @b AS b ON b.fk = a.pk
LEFT JOIN @c AS c ON c.fk = a.pk
GROUP BY a.pk
WITH ROLLUP

-- Right way
SELECT a.pk,
SUM(b.i) AS SumAct,
SUM(c.j) AS SumDebt
FROM (
SELECT pk
FROM @a
GROUP BY pk
)AS a
LEFT JOIN (
SELECT fk,
SUM(i) AS i
FROM @b
GROUP BY fk
) AS b ON b.fk = a.pk
LEFT JOIN (
SELECT fk,
SUM(j) AS j
FROM @c
GROUP BY fk
) AS c ON c.fk = a.pk
GROUP BY a.pk
WITH ROLLUP[/code]

E 12°55'05.63"
N 56°04'39.26"

wsqlw
Starting Member

8 Posts

Posted - 2008-10-01 : 11:02:56

I told my teacher your sugestions and this is what he said: "That's ok... but it's about a simple joining of 3 almost identical tables... optimize your query - you don't need group by..."
Any help?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-01 : 11:14:57

Your teacher assumes a lot with that sentence.

1) He assumes all three tables always have some number of records
2) He assumes there are no duplicate records in any of the three tables
3) He assumes same ID is present in all three tables

If yes on all three estimates, this will do

SELECT		t1.ID,
		t1.Name,
		t1.Votes AS VotesTable1,
		t2.Votes AS VotesTable2,
		t3.Votes AS VotesTable3,
		t1.Votes + t2.Votes + t3.Votes AS Total
FROM		Table1 AS t1
INNER JOIN	Table2 AS t2 ON t2.ID = t1.ID
INNER JOIN	Table3 AS t3 ON t3.ID = t2.ID

And now we know why some teach and some don't.

E 12°55'05.63"
N 56°04'39.26"

Ifor
Aged Yak Warrior

700 Posts

Posted - 2008-10-01 : 11:27:14

Do you mean the following?

SELECT T1.[ID]
    ,T1.[Name]
    ,T1.Votes AS VotesTable1
    ,T2.Votes AS VotesTable2
    ,T3.Votes AS VotesTable3
    ,T1.Votes + T2.Votes + T3.Votes AS Total
FROM Table1 T1
    JOIN Table2 T2
        ON T1.[ID] = T2.[ID]
    JOIN Table3 T3
        ON T1.[ID] = T3.[ID]

[Edit] Opps. Got diverted and forgot to check before posting.

wsqlw
Starting Member

8 Posts

Posted - 2008-10-02 : 10:54:38

I told my teacher what you sugested me and he said that it's ok. Thank's everyone!

SELECT t1.ID,
t1.Name,
t1.Votes AS VotesTable1,
t2.Votes AS VotesTable2,
t3.Votes AS VotesTable3,
t1.Votes + t2.Votes + t3.Votes AS Total
FROM Table1 AS t1
INNER JOIN Table2 AS t2 ON t2.ID = t1.ID
INNER JOIN Table3 AS t3 ON t3.ID = t2.ID

and...

SELECT
a.ID,
a.Votes,
b.Votes,
c.Votes,
a.Votes + b.Votes + c.Votes as "Total"
FROM Table1 a
LEFT JOIN Table2 b
ON b.ID = a.ID
LEFT JOIN Table3 c
ON c.ID = a.ID

It works!
Thanks again...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources