Count Function with a subquery

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

Count Function with a subquery

Author

Topic

sqlschool
Starting Member

3 Posts

Posted - 2012-02-29 : 00:37:41

Can you please advise what is wrong with my query. I am trying to find a solution to provide a list of account numbers, the number of trans done between a certain time, the total sum of those trans and a count of all deals done on that account.

I can run them separately and they work, but I am unable to merge the two queries. Any advice?

select acct.accountno,
query1.countdealsoneyear,
query1.sumamtoneyear,
query2.countalldeals

from (
select acct.accountno,
count(acdw.moveno) as COUNTDEALSONEYEAR,
sum(acdw.ccyamt) as SUMAMTONEYEAR
from acct join acdw on (acct.accountno=acdw.accountno)
where acdw.vdate between acct.opendate+365
and acct.opendate+730
group by acct.accountno) as Query1,

from (
select acct.accountno,
count(acdw.ccyamt) as COUNTALLDEALS
from acct join acdw on (acct.accountno=acdw.accountno)

group by acct.accountno) as Query2

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-29 : 10:18:14

SQL syntax does not allow you to structure the query the way you have done it. Instead you can use what I am showing below. You may be able to use a UNION clause to merge the two as well.

SELECT
	COALESCE(query1.accountno,Query2.accountno) AS accountno,
	query1.countdealsoneyear,
	query1.sumamtoneyear,
	query2.countalldeals
FROM
(
	    SELECT
	    	acct.accountno,
	    	COUNT(acdw.moveno) AS COUNTDEALSONEYEAR,
	    	SUM(acdw.ccyamt) AS SUMAMTONEYEAR
	    FROM
	    	acct
	    	JOIN acdw
	    		ON  (acct.accountno = acdw.accountno)
	    WHERE
	    	acdw.vdate BETWEEN acct.opendate + 365
	    	AND acct.opendate + 730
	    GROUP BY
	    	acct.accountno
) AS query1
FULL JOIN 
	    SELECT
	    	acct.accountno,
	    	COUNT(acdw.ccyamt) AS COUNTALLDEALS
	    FROM
	    	acct
	    	JOIN acdw
	    		ON  (acct.accountno = acdw.accountno)
	    GROUP BY
	    	acct.accountno
	) AS Query2
ON query1.accountno = query2.accountno;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-29 : 11:43:02

wont this suffice?


select acct.accountno,
count(acdw.ccyamt) as COUNTALLDEALS,
count(CASE when acdw.vdate between acct.opendate+365 and acct.opendate+730 then acdw.moveno else null end) as COUNTDEALSONEYEAR,
sum(CASE when acdw.vdate between acct.opendate+365 and acct.opendate+730 then acdw.ccyamt else 0 end ) as SUMAMTONEYEAR
from acct join acdw on (acct.accountno=acdw.accountno)
group by acct.accountno

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlschool
Starting Member

3 Posts

Posted - 2012-02-29 : 18:27:29

[quote]Originally posted by visakh16

wont this suffice?


select acct.accountno,
count(acdw.ccyamt) as COUNTALLDEALS,
count(CASE when acdw.vdate between acct.opendate+365 and acct.opendate+730 then acdw.moveno else null end) as COUNTDEALSONEYEAR,
sum(CASE when acdw.vdate between acct.opendate+365 and acct.opendate+730 then acdw.ccyamt else 0 end ) as SUMAMTONEYEAR
from acct join acdw on (acct.accountno=acdw.accountno)
group by acct.accountno

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

[/Thanks so much for your help,this was so much easier than a subquery]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-29 : 22:35:31

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources