Solved

sql, classic asp, asp.net

Posted on 2012-03-30
4
221 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Parse field in SQL View 15 96
Replace &lt; with < 14 54
Entity Framework 7 27
SQL Query resolving a string conversion issue 26 37
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

932 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now