fabzster187
asked on
MYSQL JOIN QUERY
Hi
I have the following query that I use to print a report that gives me all policies sold during a period of time per store.
This works great, however I need to also count the amount of policies done per store.
How to I change this query?
I have the following query that I use to print a report that gives me all policies sold during a period of time per store.
SELECT tblpolicies.PolicyNumber
, tblpolicies.StoreId
, tblpolicies.ConsultantFullName
, tblpolicies.DateReceived
, tblpolicies.ClientFullName
, tblpolicies.Comment
, tblpolicies.Query
, tblpolicies.PolicyStatus
, tblpolicies.DateModified
, Groups.GroupName
, Groups.StoreName
, Groups.StoreTarget
, Groups.StoreManager
, Groups.PortfolioName
, Groups.StoreStatus
, Groups.RepName
FROM tblpolicies
LEFT OUTER
JOIN ( SELECT StoreId,
StoreName, StoreManager, GroupName, StoreTarget, PortfolioName, StoreStatus, RepName
FROM tblstores
GROUP
BY StoreId ) AS Groups
ON tblpolicies.StoreId = Groups.StoreId
WHERE DateReceived BETWEEN '2011-01-01' AND '2011-01-31'
AND RepName='{$_SESSION['RepNameReport']}' AND StoreStatus='ACTIVE' ORDER BY StoreId, DateReceived") or die(mysql_error());
This works great, however I need to also count the amount of policies done per store.
How to I change this query?
the query above should give you total policies by store but it will do so on every line. For example, if store id sold 4 policies then you would 4 rows in your results (one for the details of each policy) and the countperstore column of all 4 rows would be 4. If you exported this to excel and did a sum of your policies there would be lots of double-counting. If that isn't an issue then it should work. The problem is that you are trying to get summary (per store) data in the same query as detailed data (tell me about each policy.)
I would suggest that you go with two separate queries (may need to check syntax on the summary query that I posted below:
I would suggest that you go with two separate queries (may need to check syntax on the summary query that I posted below:
SELECT tblstores.StoreName,
count(policynumber) as 'Policies'
FROM tblpolicies
LEFT OUTER JOIN tblstores ON tblpolicies.StoreId = tblstores.StoreId
WHERE DateReceived BETWEEN '2011-01-01' AND '2011-01-31'
AND RepName='{$_SESSION['RepNameReport']}' AND StoreStatus='ACTIVE' ORDER BY StoreId, DateReceived") or die(mysql_error());
group by
tblstores.Storename
You can also try like this.
SELECT tblpolicies.PolicyNumber,
tblpolicies.StoreId,
tblpolicies.ConsultantFullName,
tblpolicies.DateReceived,
tblpolicies.ClientFullName,
tblpolicies.COMMENT,
tblpolicies.Query,
tblpolicies.PolicyStatus,
tblpolicies.DateModified,
Groups.GroupName,
Groups.StoreName,
Groups.StoreTarget,
Groups.StoreManager,
Groups.PortfolioName,
Groups.StoreStatus,
Groups.RepName,
t1.Cnt_policynumber
FROM tblpolicies
INNER JOIN ( SELECT StoreID,
COUNT(policynumber) Cnt_policynumber
FROM tblpolicies
GROUP BY StoreID) AS t1
ON tblpolicies.StoreID = t1.StoreID
LEFT OUTER JOIN ( SELECT StoreId,
StoreName,
StoreManager,
GroupName,
StoreTarget,
PortfolioName,
StoreStatus,
RepName
FROM tblstores
GROUP BY StoreId) AS Groups
ON tblpolicies.StoreId = Groups.StoreId
WHERE DateReceived BETWEEN '2011-01-01' AND '2011-01-31'
AND RepName='{$_SESSION['RepNameReport']}'
AND StoreStatus='ACTIVE' ORDER BY StoreId, DateReceived")
OR die(mysql_error());
ASKER
My problem is that query is used for a report that prints to pdf
So I need a total per store to be printed out on each heading
So I need a total per store to be printed out on each heading
You can use my query to create your report, but the formatting has to be done with PHP, not the query.
I would suggest you take a look at this link
http://www.phpfreaks.com/forums/mysql-help/calculating-grand-total-from-multiple-queries/
I would suggest you take a look at this link
http://www.phpfreaks.com/forums/mysql-help/calculating-grand-total-from-multiple-queries/
ASKER
Yeah I can format the total in PHP on the report no problem.
I tested the above queries and they give me the incorrect totals though (they are way out)
for example
the one group did only 17 policies in total and a store in that group who only did 1 policy shows that the total is 617.
Any more info I can provide to assist
I tested the above queries and they give me the incorrect totals though (they are way out)
for example
the one group did only 17 policies in total and a store in that group who only did 1 policy shows that the total is 617.
Any more info I can provide to assist
Provide some sample data with expected result.
ASKER
ok
I use the above query currently to give me results for a report that will give me all the stores that sold policies from a group during a period.
store name
store details (managers etc)
then below that I print in PHP the actual policies in a list (attached pdf) now I would like the
totals for the specific store to be displayed in the header of the store(as long as I get the result from the Mysql query I can place it there with php)
I exported some data to excel from the tblstores and tblpolicies tables so that you may see the structure of the data.
Hope this helps
daily-report.pdf
tblstores.xls
tblpolicies.xls
I use the above query currently to give me results for a report that will give me all the stores that sold policies from a group during a period.
store name
store details (managers etc)
then below that I print in PHP the actual policies in a list (attached pdf) now I would like the
totals for the specific store to be displayed in the header of the store(as long as I get the result from the Mysql query I can place it there with php)
I exported some data to excel from the tblstores and tblpolicies tables so that you may see the structure of the data.
Hope this helps
daily-report.pdf
tblstores.xls
tblpolicies.xls
ASKER
more info supplied, why no response?
please assist guy's I am trying but cannot solve this
please assist guy's I am trying but cannot solve this
ASKER
Ok I solved this myself...
here is the solution:
here is the solution:
SELECT tblpolicies.PolicyNumber
, tblpolicies.StoreId
, tblpolicies.ConsultantFullName
, tblpolicies.DateReceived
, tblpolicies.ClientFullName
, tblpolicies.Comment
, tblpolicies.Query
, tblpolicies.PolicyStatus
, tblpolicies.DateModified
, Groups.GroupName
, Groups.StoreName
, Groups.StoreTarget
, Groups.StoreManager
, Groups.PortfolioName
, Groups.StoreStatus
, Groups.RepName
, Policies.total_policies
FROM tblstores AS Groups
INNER
JOIN tblpolicies
ON tblpolicies.StoreId = Groups.StoreId
AND tblpolicies.PolicyStatus='ACTIVE'
AND tblpolicies.DateReceived BETWEEN '{$_SESSION['StartDateRepReport']}'
AND '{$_SESSION['EndDateRepReport']}'
INNER
JOIN ( SELECT StoreId
, COUNT(*) AS total_policies
FROM tblpolicies
WHERE DateReceived BETWEEN '{$_SESSION['StartDateRepReport']}'
AND '{$_SESSION['EndDateRepReport']}'
AND tblpolicies.PolicyStatus = 'ACTIVE'
GROUP
BY StoreId ) AS Policies
ON Policies.StoreId = Groups.StoreId
WHERE Groups.RepName='{$_SESSION['RepNameReport']}'
AND Groups.StoreStatus='ACTIVE'
ORDER
BY Groups.StoreId
, tblpolicies.DateReceived
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
if that doesn't work, please post some sample data and the expected result.
Open in new window