Solved

SQL query count the rows returns

Posted on 2014-11-11
20
261 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

705 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