sum from a sub select help - 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

SQL Server Development (2000)

sum from a sub select help

Author

Topic

Hommer
Aged Yak Warrior

808 Posts

Posted - 2004-10-12 : 13:13:03

I have a select sql to get me the duplicated rows as follow:

select tbilled, tracking, count(*)
from sept904invoice group by tracking, tbilled having count(*)>1

Now I want to get a sum on the tbilled, but could not.
I have tried Select INTO, Select sum(tbilled) where tracking in (select...) or Where tracking exist...

The reqirement is to get a sum on a billed charge where tracking number is duplicated.

Thanks!

Hommer

surefooted
Posting Yak Master

188 Posts

Posted - 2004-10-12 : 13:20:36

[code]select sum(tbilled), tracking, count(*)
from sept904invoice
group by tracking
having count(*)>1[/code]

??

-Jon
Now a "Yak Posting Veteran".

Hommer
Aged Yak Warrior

808 Posts

Posted - 2004-10-12 : 13:38:42

They want to sum all records with duplicated tracking number. The sum is not BY tracking.

That is why I tried to use sub select to get the duplicates first then try to sum.

X002548
Not Just a Number

15586 Posts

Posted - 2004-10-12 : 13:51:39

[doooh]


SELECT SUM(tbilled) FROM (
  SELECT tbilled, tracking, count(*) 
    FROM sept904invoice 
GROUP BY tracking, tbilled 
  HAVING count(*)>1) AS XXX

[/doooh]

Brett

8-)

Hommer
Aged Yak Warrior

808 Posts

Posted - 2004-10-12 : 14:01:53

I have tried this at very begining:

SELECT SUM(convert(money, tbilled)) FROM (
SELECT tbilled, tracking, count(*)
FROM sept904invoice
GROUP BY tracking, tbilled
HAVING count(*)>1)

I need to throw in the convert because the field is of varchar type.

And I got: Line 5: Incorrect syntax near ')'.

That is the best I could get.

X002548
Not Just a Number

15586 Posts

Posted - 2004-10-12 : 14:31:43

Actually I think count needs to be label in the derived table..

Brett

8-)

Hommer
Aged Yak Warrior

808 Posts

Posted - 2004-10-12 : 14:32:09

I got it. two steps.

SELECT tbilled, tracking, count(*) as countNo into MyTemp FROM sept904invoice
GROUP BY tracking, tbilled HAVING count(*)>1

select sum(convert(money, tbilled)) from mytemp

Without "as countNo", the first part got tossed complaining No column was specified...

Thanks!

X002548
Not Just a Number

15586 Posts

Posted - 2004-10-12 : 14:42:18

[code]
USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE sept904invoice99(tbilled varchar(15), tracking varchar(15))
GO

INSERT INTO sept904invoice99(tbilled, tracking)
SELECT 1, 1 UNION ALL
SELECT 2, 1 UNION ALL
SELECT 3, 1 UNION ALL
SELECT 4, 1 UNION ALL
SELECT 4, 1 UNION ALL
SELECT 5, 1 UNION ALL
SELECT 5, 1 UNION ALL
SELECT 5, 1 UNION ALL
SELECT 6, 1 UNION ALL
SELECT 7, 2 UNION ALL
SELECT 7, 2GO
GO

SELECT CONVERT(money,tbilled) AS tbilled, tracking, count(*) AS DupCounts
FROM sept904invoice99
GROUP BY tracking, tbilled
HAVING count(*)>1

SELECT SUM(tbilled) SUM_tbilled FROM (
SELECT CONVERT(money,tbilled) AS tbilled, tracking, count(*) AS DupCounts
FROM sept904invoice99
GROUP BY tracking, tbilled
HAVING count(*)>1) AS XXX
GO

SET NOCOUNT OFF
DROP TABLE sept904invoice99
GO

[/code]

See what I get for not testing...doesn't make much sense though...

Brett

8-)

Hommer
Aged Yak Warrior

808 Posts

Posted - 2004-10-12 : 16:47:54

That is all these XXX for. Without "as xxx", it complains about incorrect syntax near the ), poor scapegoat.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources