Solved

SQL Query --- counting by groups

Posted on 2014-11-10
5
125 Views
Last Modified: 2014-11-13
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?
0
Comment
Question by:vbnetcoder
5 Comments
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 40433593
Something like:

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

Author Comment

by:vbnetcoder
ID: 40433675
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
 
LVL 49

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 40434474
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
 
LVL 32

Expert Comment

by:awking00
ID: 40438076
>>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
 

Author Closing Comment

by:vbnetcoder
ID: 40439571
ty
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question