Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 241
  • Last Modified:

sql, classic asp, asp.net

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
Webboy2008
Asked:
Webboy2008
  • 2
1 Solution
 
ThomasianCommented:
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
 
Anthony PerkinsCommented:
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
 
Webboy2008Author Commented:
Both guys's codes do not work...
0
 
Anthony PerkinsCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now