Sum Query Help

G

Guest

I have a query that works as I want except that I want total by
agent by state. It is returning total by agent by state by month.

My query:

SELECT DISTINCT
TOP 100 PERCENT dbo.tblAgents.AgentName,
dbo.tblProducers.State, dbo.tblAgents.AgentRep, COUNT
(dbo.tblMasterPolicy.PolicyNumber) AS [Policy Count],
dbo.tblCarrierProducts.CarrierID
FROM dbo.tblAgents INNER JOIN
dbo.tblReportData ON dbo.tblAgents.AgentID =
dbo.tblReportData.AgentID INNER JOIN
dbo.tblProducers ON dbo.tblReportData.ProducerID =
dbo.tblProducers.ProducerID INNER JOIN
dbo.tblMasterPolicy ON dbo.tblReportData.PolicyID =
dbo.tblMasterPolicy.PolicyID INNER JOIN
dbo.tblCarrierProducts ON dbo.tblReportData.ProductID
= dbo.tblCarrierProducts.ProductID
GROUP BY dbo.tblAgents.AgentName, dbo.tblProducers.State,
dbo.tblAgents.AgentRep, dbo.tblCarrierProducts.CarrierID,
dbo.tblMasterPolicy.ExportDate
HAVING (dbo.tblCarrierProducts.CarrierID LIKE 'fcic') AND
(dbo.tblMasterPolicy.ExportDate >= CONVERT(DATETIME, '2006-01-01
00:00:00', 102)) OR
(dbo.tblCarrierProducts.CarrierID LIKE 'nbic') AND
(dbo.tblMasterPolicy.ExportDate >= CONVERT(DATETIME, '2006-01-01
00:00:00', 102))
ORDER BY dbo.tblAgents.AgentName, dbo.tblProducers.State

Results sniped:

A. Philip Brown & Asscoiates MD 1 FCIC
A. Philip Brown & Asscoiates VA 3 FCIC
Accelerated Solutions MA 6 FCIC
Accelerated Solutions MA 1 FCIC
Advocate Solutions IL 160 FCIC
Advocate Solutions IL 172 FCIC
Advocate Solutions IL 173 FCIC
Advocate Solutions IL 47 FCIC
Advocate Solutions IL 152 FCIC
Advocate Solutions IL 71 FCIC
Advocate Solutions IL 185 FCIC
---SNIP----

As you can see, advocate solutions is listed more than once for IL
and I need it totaled for IL on one line for all the months.

I am sure that it is most likely something simple that I am
overlooking.

Thanks for the assistance.
 
G

Gary Walter

What happens if you drop ExportDate from GROUP BY
and include in a WHERE clause?

in fact, will your data produce same counts if

<snip>
dbo.tblCarrierProducts ON dbo.tblReportData.ProductID
= dbo.tblCarrierProducts.ProductID
WHERE
dbo.tblCarrierProducts.CarrierID IN ('FCIC', 'NBIC')
AND
dbo.tblMasterPolicy.ExportDate >= CONVERT(DATETIME, '2006-01-01
00:00:00', 102))
GROUP BY dbo.tblAgents.AgentName, dbo.tblProducers.State,
dbo.tblAgents.AgentRep, dbo.tblCarrierProducts.CarrierID
ORDER BY dbo.tblAgents.AgentName, dbo.tblProducers.State
 
G

Guest

That did it. Thanks.

Gary Walter said:
What happens if you drop ExportDate from GROUP BY
and include in a WHERE clause?

in fact, will your data produce same counts if

<snip>
dbo.tblCarrierProducts ON dbo.tblReportData.ProductID
= dbo.tblCarrierProducts.ProductID
WHERE
dbo.tblCarrierProducts.CarrierID IN ('FCIC', 'NBIC')
AND
dbo.tblMasterPolicy.ExportDate >= CONVERT(DATETIME, '2006-01-01
00:00:00', 102))
GROUP BY dbo.tblAgents.AgentName, dbo.tblProducers.State,
dbo.tblAgents.AgentRep, dbo.tblCarrierProducts.CarrierID
ORDER BY dbo.tblAgents.AgentName, dbo.tblProducers.State


Ted said:
I have a query that works as I want except that I want total by
agent by state. It is returning total by agent by state by month.

My query:

SELECT DISTINCT
TOP 100 PERCENT dbo.tblAgents.AgentName,
dbo.tblProducers.State, dbo.tblAgents.AgentRep, COUNT
(dbo.tblMasterPolicy.PolicyNumber) AS [Policy Count],
dbo.tblCarrierProducts.CarrierID
FROM dbo.tblAgents INNER JOIN
dbo.tblReportData ON dbo.tblAgents.AgentID =
dbo.tblReportData.AgentID INNER JOIN
dbo.tblProducers ON dbo.tblReportData.ProducerID =
dbo.tblProducers.ProducerID INNER JOIN
dbo.tblMasterPolicy ON dbo.tblReportData.PolicyID =
dbo.tblMasterPolicy.PolicyID INNER JOIN
dbo.tblCarrierProducts ON dbo.tblReportData.ProductID
= dbo.tblCarrierProducts.ProductID
GROUP BY dbo.tblAgents.AgentName, dbo.tblProducers.State,
dbo.tblAgents.AgentRep, dbo.tblCarrierProducts.CarrierID,
dbo.tblMasterPolicy.ExportDate
HAVING (dbo.tblCarrierProducts.CarrierID LIKE 'fcic') AND
(dbo.tblMasterPolicy.ExportDate >= CONVERT(DATETIME, '2006-01-01
00:00:00', 102)) OR
(dbo.tblCarrierProducts.CarrierID LIKE 'nbic') AND
(dbo.tblMasterPolicy.ExportDate >= CONVERT(DATETIME, '2006-01-01
00:00:00', 102))
ORDER BY dbo.tblAgents.AgentName, dbo.tblProducers.State

Results sniped:

A. Philip Brown & Asscoiates MD 1 FCIC
A. Philip Brown & Asscoiates VA 3 FCIC
Accelerated Solutions MA 6 FCIC
Accelerated Solutions MA 1 FCIC
Advocate Solutions IL 160 FCIC
Advocate Solutions IL 172 FCIC
Advocate Solutions IL 173 FCIC
Advocate Solutions IL 47 FCIC
Advocate Solutions IL 152 FCIC
Advocate Solutions IL 71 FCIC
Advocate Solutions IL 185 FCIC
---SNIP----

As you can see, advocate solutions is listed more than once for IL
and I need it totaled for IL on one line for all the months.

I am sure that it is most likely something simple that I am
overlooking.

Thanks for the assistance.
Click to expand...
Click to expand...
 
Top