asked on

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

ASKER CERTIFIED SOLUTION

Thomasian

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anthony Perkins

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

Webboy2008

ASKER

Both guys's codes do not work...

Anthony Perkins

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.