SQL query count the rows returns

I have the following query that is working fine.  The only thing i want to do now is a row for the total records returned

            SELECT  P.[Project Name], Count(DP.Document_ID)
  FROM [MyData].[dbo].[Document_Project] DP
   INNER JOIN  [MyData].[dbo].[Document_Files] DF
    ON DF.Document_ID = dp.Document_ID
      INNER JOIN [MyData].[dbo].Project P
          ON P.ID = dp.Project_ID  
          Group By P.[Project Name]  


So, the data would look something like this:

Project         count   Total
AA Project      2         7
bb project      2         7
cc Project      3         7
dd              2         7
vbnetcoderAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

vbnetcoderAuthor Commented:
Actually the total column should say 9 ...dah!  meaning the sum of all the counts
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
add at the end of line 1

, Count(DP.Document_ID) OVER() as Total

Open in new window

0
vbnetcoderAuthor Commented:
I get an error:

Column  Document_Project.Document_ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
According to my understanding, you don't have that as a column.

You have code something like:

            SELECT  P.[Project Name], Count(DP.Document_ID) as MyCount, Count(DP.Document_ID) OVER() as MyTotal
  FROM [MyData].[dbo].[Document_Project] DP
   INNER JOIN  [MyData].[dbo].[Document_Files] DF
    ON DF.Document_ID = dp.Document_ID
      INNER JOIN [MyData].[dbo].Project P
          ON P.ID = dp.Project_ID  
          Group By P.[Project Name]  

Open in new window


Is this correct? If not, please post your current code.
0
vbnetcoderAuthor Commented:
This is the code:

       SELECT  P.[Project Name], Count(DP.Document_ID) as MyCount, Count(DP.Document_ID) OVER() as MyTotal
  FROM [Document_Project] DP
   INNER JOIN  [Document_Files] DF
    ON DF.Document_ID = dp.Document_ID
      INNER JOIN Project P
          ON P.ID = dp.Project_ID  
          Group By P.[Project Name]  

and i get this error:

Column 'Document_Project.Document_ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Which version of SQL Server do you have? 2008?
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Try this instead as the first line:

SELECT  P.[Project Name], Count(DP.Document_ID) as MyCount, Count(*) OVER() as MyTotal
0
vbnetcoderAuthor Commented:
2012 express
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Try this as your first line:

       SELECT  P.[Project Name], Count(DP.Document_ID) as MyCount, Count(DP.Document_ID) OVER(ORDER BY P.[Project Name]) as MyTotal
0
vbnetcoderAuthor Commented:
Same error
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Let's try something different. If the first part of your code works right, try this:

With MyTable as 
(SELECT  P.[Project Name] as Project, Count(DP.Document_ID) as MyCount
  FROM [MyData].[dbo].[Document_Project] DP
   INNER JOIN  [MyData].[dbo].[Document_Files] DF
    ON DF.Document_ID = dp.Document_ID
      INNER JOIN [MyData].[dbo].Project P
          ON P.ID = dp.Project_ID  
          Group By P.[Project Name])
Select Project, MyCount, sum(MyCount) OVER(Order by Project) as MyTotal
From MyTable

Open in new window

0
vbnetcoderAuthor Commented:
That is close... this is the result.  It would be ideal if the 3rd row was alway the total so in this case 9.

AA Project      2      2
bb project      2      4
ccc Project      3      7
sss                  2      9
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Change line 9 to

Select Project, MyCount, sum(MyCount) OVER(Order by Project ROWS UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as MyTotal
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
What's the difference of this question with this one http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_28554552.html?
0
vbnetcoderAuthor Commented:
It says Incorrect syntax near the keyword 'AND'.
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
To whom are you addressing your comments? My code does not use the word "AND".
0
vbnetcoderAuthor Commented:
Philip,

This line:

Select Project, MyCount, sum(MyCount) OVER(Order by Project ROWS UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as MyTotal
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Please change to:

Select Project, MyCount, sum(MyCount) OVER(Order by Project ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as MyTotal
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vbnetcoderAuthor Commented:
YES!!!!!! Thank you!
0
vbnetcoderAuthor Commented:
ty
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.