Solved

SQL query count the rows returns

Posted on 2014-11-11
20
260 Views
Last Modified: 2014-11-11
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
0
Comment
Question by:vbnetcoder
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 9
20 Comments
 

Author Comment

by:vbnetcoder
ID: 40434748
Actually the total column should say 9 ...dah!  meaning the sum of all the counts
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40434766
add at the end of line 1

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

Open in new window

0
 

Author Comment

by:vbnetcoder
ID: 40434776
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40434778
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
 

Author Comment

by:vbnetcoder
ID: 40434786
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
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40434789
Which version of SQL Server do you have? 2008?
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40434795
Try this instead as the first line:

SELECT  P.[Project Name], Count(DP.Document_ID) as MyCount, Count(*) OVER() as MyTotal
0
 

Author Comment

by:vbnetcoder
ID: 40434798
2012 express
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40434806
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
 

Author Comment

by:vbnetcoder
ID: 40434812
Same error
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40434815
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
 

Author Comment

by:vbnetcoder
ID: 40434832
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
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40434835
Change line 9 to

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

Expert Comment

by:Vitor Montalvão
ID: 40434847
What's the difference of this question with this one http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_28554552.html?
0
 

Author Comment

by:vbnetcoder
ID: 40434908
It says Incorrect syntax near the keyword 'AND'.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40434914
To whom are you addressing your comments? My code does not use the word "AND".
0
 

Author Comment

by:vbnetcoder
ID: 40434917
Philip,

This line:

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

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40434919
Please change to:

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

Author Comment

by:vbnetcoder
ID: 40434935
YES!!!!!! Thank you!
0
 

Author Closing Comment

by:vbnetcoder
ID: 40434936
ty
0

Featured Post

Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Time out only from one specific computer client 15 52
SQL Server 2012 to SQL Server 2016 24 54
SQL Job Failed 6 30
Delete duplicates from SQL Server table 2 26
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

732 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