Solved

SQL Query --- counting by groups

Posted on 2014-11-10
5
118 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 25

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 45

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 31

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video discusses moving either the default database or any database to a new volume.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

707 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now