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?
vbnetcoderAsked:
Who is Participating?
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
I don't know why you need to have the TotalDocuments in all rows since it will be always the same value but here's one solution that I personally don't like:
SELECT  P.[Project Name],   Count(DP.Document_ID) AS DocumentCount , 
           (SELECT COUNT(1) FROM [MyTableReports].[dbo].[Document_Project]) AS TotalDocuments
   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]  

Open in new window

0
 
Shaun KlineLead Software EngineerCommented:
Something like:

SELECT ProjectID, COUNT(*)
FROM Projects
GROUP BY ProjectID WITH ROLLUP
0
 
vbnetcoderAuthor Commented:
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?
0
 
awking00Commented:
>>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?
0
 
vbnetcoderAuthor Commented:
ty
0
All Courses

From novice to tech pro — start learning today.