query 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 2005 Forums

Transact-SQL (2005)

query help

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-11-01 : 03:28:37

my current query is

select customerid,sum(duration) as totalduration,sum(charge) as totalcharges,from billing group by customerid order by customerid

now i have another table called
payments with a customerid and paymentamount (customerid is a varchar field)
How can I add to the above query to also return me the sum(paymentamount) for that customer?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-01 : 03:35:12

[code]SELECT b.customerid,
SUM(duration) AS totalduration,
SUM(charge) AS totalcharges,
SUM(paymentamount) AS paymentamount
FROM billing b INNER JOIN payments p
ON b.customerid = p.customerid
GROUP BY b.customerid
ORDER BY b.customerid[/code]

KH
[spoiler]Time is always against us[/spoiler]

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-11-01 : 03:42:57

thanks this shows only records with a payment amount

how can i show all records and if no payments record then it should just show 0?

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-11-01 : 03:44:01

got it --think i just needed a left join

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-01 : 03:44:32

yes. just change to LEFT JOIN

KH
[spoiler]Time is always against us[/spoiler]

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-11-01 : 04:04:09

this is very strange

paymentsamounts has 3 records

012022650651 1 52.299999 this is a test 2007-11-01 03:23:00
01133220001 2 33.330002 test 2007-11-01 03:26:00
01133220001 3 10.0 TEST 2007-11-01 04:01:00

but in the results from the above query under the customerid of 01133220001 it shows
173.32000732421875
instead of 10+33.33002 any ideas? this is strange - where could it be getting this number from?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-01 : 05:04:27

Oh it is a one to many relationship between the 2 tables
use derived table to group by customerid

SELECT b.customerid, 
       SUM(duration) AS totalduration,
       SUM(charge) AS totalcharges,
       SUM(paymentamount) AS paymentamount
FROM   billing b LEFT JOIN 
       (
           SELECT customerid, paymentamount = SUM(paymentamount)
           FROM   payments 
           GROUP BY customerid
       ) p
ON     b.customerid = p.customerid
GROUP BY b.customerid 
ORDER BY b.customerid

KH
[spoiler]Time is always against us[/spoiler]

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-11-01 : 05:11:19

tried that it's stil showing 173 where is this number coming from? it should be adding the amounts?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-01 : 05:22:06

OK. Try this

SELECT	b.customerid,
	b.totalduration
	b,totalcharges,
	ISNULL(p.paymentamount, 0) AS paymentamount
FROM
(
	SELECT 	b.customerid, 
       		SUM(duration) AS totalduration,
       		SUM(charge) AS totalcharges,
	FROM   	billing b
	GROUP BY b.customerid
) b
LEFT JOIN 
(
	SELECT customerid, paymentamount = SUM(paymentamount)
	FROM   payments 
	GROUP BY customerid
) p
ON     b.customerid = p.customerid
GROUP BY b.customerid 
ORDER BY b.customerid

KH
[spoiler]Time is always against us[/spoiler]

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-11-01 : 05:33:15

Server: Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'FROM'.
Server: Msg 170, Level 15, State 1, Line 18
Line 18: Incorrect syntax near 'p'.

and i fixed a few things i saw

my updated query is

SELECT b.customerid,
b.totalduration,
b.totalcharges,
ISNULL(p.paymentamount, 0) AS paymentamount
FROM
(
SELECT b.customerid,
SUM(duration) AS totalduration,
SUM(charge) AS totalcharges,
FROM billing
GROUP BY b.customerid
) b
LEFT JOIN
(
SELECT customerid, paymentamount = SUM(paymentamount)
FROM payments
GROUP BY customerid
) p
ON b.customerid = p.customerid
GROUP BY b.customerid
ORDER BY b.customerid

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-01 : 05:43:46

[code]SELECT b.customerid,
b.totalduration,
b.totalcharges,
ISNULL(p.paymentamount, 0) AS paymentamount
FROM
(
SELECT customerid,
SUM(duration) AS totalduration,
SUM(charge) AS totalcharges,
FROM billing
GROUP BY customerid
) b
LEFT JOIN
(
SELECT customerid, paymentamount = SUM(paymentamount)
FROM payments
GROUP BY customerid
) p
ON b.customerid = p.customerid
GROUP BY b.customerid
ORDER BY b.customerid[/code]

KH
[spoiler]Time is always against us[/spoiler]

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-11-01 : 05:52:42

i removed the comma after total charges but i'm now getting

Server: Msg 8120, Level 16, State 1, Line 1
Column 'b.totalduration' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'b.totalcharges' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'p.paymentamount' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-01 : 05:55:37

this error message is coming from which query ?

KH
[spoiler]Time is always against us[/spoiler]

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-11-01 : 05:59:32

SELECT b.customerid,
b.totalduration,
b.totalcharges,
ISNULL(p.paymentamount, 0) AS paymentamount
FROM
(
SELECT customerid,
SUM(duration) AS totalduration,
SUM(charge) AS totalcharges
FROM billing
GROUP BY customerid
) b
LEFT JOIN
(
SELECT customerid, paymentamount = SUM(paymentamount)
FROM payments
GROUP BY customerid
) p
ON b.customerid = p.customerid
GROUP BY b.customerid
ORDER BY b.customerid

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-01 : 06:02:16

Sorry, C&P error

SELECT 	b.customerid,
	b.totalduration,
	b.totalcharges,
	ISNULL(p.paymentamount, 0) AS paymentamount
FROM
(
	SELECT 	customerid,
		SUM(duration) AS totalduration,
		SUM(charge) AS totalcharges,
	FROM 	billing
	GROUP BY customerid
) b
LEFT JOIN
(
	SELECT customerid, paymentamount = SUM(paymentamount)
	FROM payments
	GROUP BY customerid
) p
ON 	b.customerid = p.customerid
GROUP BY b.customerid
ORDER BY b.customerid

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources