asked on

SQL Query --- counting by groups

I have a table with data that looks something like this.

Project ID DocumenID

1 1
1 2
1 3
2 4
3 5
3 6

I need to create a sql statement that will give me a count of ALL the documents as well as ammount of documents
per project. Any clue how?

Shaun Kline

Something like:

SELECT ProjectID, COUNT(*)
FROM Projects
GROUP BY ProjectID WITH ROLLUP

vbnetcoder

ASKER

This is the exact query:

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

And it returns this:

Project Name      DocumentCount
AA Project      1
BB project      2
CC roject      2
ttttttt        1

I need it to return:

Project Name      DocumentCount Total Documents
AA Project        1 5
BB project        2 5
CC roject        2 5
ttttttt        1 5

How?

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

awking00

>>I need it to return:
Project Name DocumentCount Total Documents
AA Project 1 5
BB project 2 5
CC roject 2 5
ttttttt 1 5 <<
Can you post the data that should return those results with some explanation as to why?

vbnetcoder

ASKER