Count from multiple tables --- Please help!!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Count from multiple tables --- Please help!!

Author

Topic

prashantpvm
Starting Member

3 Posts

Posted - 2006-05-06 : 08:07:15

Hi,
I am building a small sales application where I would like to get the number of opportunities created in multiple regions. The QUERY that I am using is as follows:
-----------------------------------------------------------
SELECT OPPORTUNITY.OPP_CREATION_MONTH AS [MONTH], Count(*) AS TOTAL,
(SELECT COUNT(*) FROM REGION INNER JOIN OPPORTUNITY ON REGION.REGION_ID = OPPORTUNITY.OPP_REGION WHERE OPPORTUNITY.OPP_CREATION_YEAR=2006 AND REGION.REGION_NAME='REG1') AS Expr1,
(SELECT COUNT(*) FROM REGION INNER JOIN OPPORTUNITY ON REGION.REGION_ID = OPPORTUNITY.OPP_REGION WHERE OPPORTUNITY.OPP_CREATION_YEAR=2006 AND REGION.REGION_NAME='REG2') AS Expr2,
(SELECT COUNT(*) FROM REGION INNER JOIN OPPORTUNITY ON REGION.REGION_ID = OPPORTUNITY.OPP_REGION WHERE OPPORTUNITY.OPP_CREATION_YEAR=2006 AND REGION.REGION_NAME='REG3') AS Expr3,
(SELECT COUNT(*) FROM REGION INNER JOIN OPPORTUNITY ON REGION.REGION_ID = OPPORTUNITY.OPP_REGION WHERE OPPORTUNITY.OPP_CREATION_YEAR=2006 AND REGION.REGION_NAME='REG4') AS Expr4
FROM OPPORTUNITY
WHERE (((OPPORTUNITY.OPP_CREATION_YEAR)=2006))
GROUP BY OPPORTUNITY.OPP_CREATION_MONTH;
-----------------------------------------------------------

What I am trying to do is get the total opportunity count for each month, followed by, the total opportunity count for the REG1 for that month, the total opportunity count for the REG2 for that month,the total opportunity count for the REG3 for that month,the total opportunity count for the REG4 for that month.

Expected output should be something like this:

Month | TOTAL | REG1 | REG2 | REG3 | REG4
--------------------------------------------
Jan 11 8 1 1 1
Feb 4 1 1 1 1

However, I am not getting the above result. Any help will be appreciated.

Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-06 : 08:15:19

you missed out the OPP_CREATINO_MONTH condition in your subquery where clause

prashantpvm
Starting Member

3 Posts

Posted - 2006-05-06 : 08:31:19

When I do that it says that I must use EXISTS in the main query. When I add exists it gives "-1" under all "REG" columns.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-06 : 08:36:15

Not sure this works in MS Access. Give it a try


SELECT OPPORTUNITY.OPP_CREATION_MONTH AS [MONTH], Count(*) AS TOTAL,
(SELECT COUNT(*) FROM REGION INNER JOIN OPPORTUNITY ON REGION.REGION_ID = OPPORTUNITY.OPP_REGION WHERE OPPORTUNITY.OPP_CREATION_YEAR=2006 AND REGION.REGION_NAME='REG1' AND OPPORTUNITY.OPP_CREATION_MONTH = a.OPP_CREATION_MONTH) AS Expr1,
(SELECT COUNT(*) FROM REGION INNER JOIN OPPORTUNITY ON REGION.REGION_ID = OPPORTUNITY.OPP_REGION WHERE OPPORTUNITY.OPP_CREATION_YEAR=2006 AND REGION.REGION_NAME='REG2') AS Expr2,
(SELECT COUNT(*) FROM REGION INNER JOIN OPPORTUNITY ON REGION.REGION_ID = OPPORTUNITY.OPP_REGION WHERE OPPORTUNITY.OPP_CREATION_YEAR=2006 AND REGION.REGION_NAME='REG3') AS Expr3,
(SELECT COUNT(*) FROM REGION INNER JOIN OPPORTUNITY ON REGION.REGION_ID = OPPORTUNITY.OPP_REGION WHERE OPPORTUNITY.OPP_CREATION_YEAR=2006 AND REGION.REGION_NAME='REG4') AS Expr4
FROM OPPORTUNITY a
WHERE (((OPPORTUNITY.OPP_CREATION_YEAR)=2006))
GROUP BY OPPORTUNITY.OPP_CREATION_MONTH;

prashantpvm
Starting Member

3 Posts

Posted - 2006-05-06 : 14:50:14

Sorry, but this doesnt work either. I have tried almost everything. Please help.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-06 : 20:10:10

Any error messages ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources