Solved

sql, classic asp, asp.net

Posted on 2012-03-30
4
229 Views
Last Modified: 2012-04-12
I have the following sql statement working fine with the attached result.
However, when the  CountOfTmpRefID = "0", the the whole line will be gone.
How can make it like "ABC" still have 0 showing ? meaning when CountofTmpRefId return null or 0, it still be able to keep all of the list of the agency name?

Thanks

SELECT        
COUNT(tblWSBondQuotes.TmpRefID) AS CountOfTmpRefID,
SUM(tblWSBondQuotes.BondAmount) AS SumOfBondAmount,
SUM(tblWSBondQuotes.GrossPrem) AS SumOfGrossPrem,
SUM(tblWSBondQuotes.Commission) AS SumOfCommission,
SUM(tblWSBondQuotes.NetPrem) AS SumOfNetPrem,
Agency.DBA,
tblWSBondQuotes.sAgencyBroker
FROM            tblWSBondQuotes INNER JOIN
                         Agency_Master ON tblWSBondQuotes.sAgencyBroker = Agency_Master.Agency_ID INNER JOIN
                         Agency ON tblWSBondQuotes.sAgencyBroker = Agency.Agency_ID
WHERE        (tblWSBondQuotes.StatusID = @StatusID) AND (Agency_Master.StatusId = 1)
GROUP BY tblWSBondQuotes.sAgencyBroker, Agency.DBA, Agency_Master.AgencyMasterId
HAVING        (Agency_Master.AgencyMasterId = @AgencyMasterId)
3-30-2012-6-49-58-PM.jpg
0
Comment
Question by:Webboy2008
  • 2
4 Comments
 
LVL 22

Accepted Solution

by:
Thomasian earned 500 total points
ID: 37789943
SELECT        
	COUNT(tblWSBondQuotes.TmpRefID) AS CountOfTmpRefID, 
	SUM(tblWSBondQuotes.BondAmount) AS SumOfBondAmount, 
	SUM(tblWSBondQuotes.GrossPrem) AS SumOfGrossPrem, 
	SUM(tblWSBondQuotes.Commission) AS SumOfCommission, 
	SUM(tblWSBondQuotes.NetPrem) AS SumOfNetPrem, 
	Agency.DBA, 
	tblWSBondQuotes.sAgencyBroker
FROM
        Agency_Master LEFT JOIN
	tblWSBondQuotes ON tblWSBondQuotes.sAgencyBroker = Agency_Master.Agency_ID AND tblWSBondQuotes.StatusID = @StatusID LEFT JOIN
	Agency ON tblWSBondQuotes.sAgencyBroker = Agency.Agency_ID
WHERE
	Agency_Master.StatusId = 1
GROUP BY
	tblWSBondQuotes.sAgencyBroker,
	Agency.DBA,
	Agency_Master.AgencyMasterId
HAVING
	Agency_Master.AgencyMasterId = @AgencyMasterId

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37793490
Something like this perhaps:
SELECT  a.DBA,
        qm.CountOfTmpRefID,
        qm.SumOfBondAmount,
        qm.SumOfGrossPrem,
        qm.SumOfCommission,
        qm.SumOfNetPrem
FROM    Agency a
        LEFT JOIN (SELECT   q.sAgencyBroker,
                            COUNT(q.TmpRefID) CountOfTmpRefID,
                            SUM(q.BondAmount) SumOfBondAmount,
                            SUM(q.GrossPrem) SumOfGrossPrem,
                            SUM(q.Commission) SumOfCommission,
                            SUM(q.NetPrem) SumOfNetPrem
                   FROM     tblWSBondQuotes q
                            INNER JOIN Agency_Master m ON q.sAgencyBroker = m.Agency_ID
                   WHERE    q.StatusID = @StatusID
                            AND m.StatusId = 1
                            AND m.AgencyMasterId = @AgencyMasterId
                   GROUP BY q.sAgencyBroker
                  ) qm ON a.Agency_ID = qm.sAgencyBroker

Open in new window

0
 

Author Comment

by:Webboy2008
ID: 37797505
Both guys's codes do not work...
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37802800
Both guys's codes do not work...
Unfortuantely this tells me nothing...

When you can spend the time to explain to me in English what you mean by "codes do not work"  I will be happy to help.  As it is, the feedback is useless.
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question