Link to home
Start Free TrialLog in
Avatar of vbnetcoder
vbnetcoder

asked on

SQL query count the rows returns

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
Avatar of vbnetcoder
vbnetcoder

ASKER

Actually the total column should say 9 ...dah!  meaning the sum of all the counts
add at the end of line 1

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

Open in new window

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.
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.
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.
Which version of SQL Server do you have? 2008?
Try this instead as the first line:

SELECT  P.[Project Name], Count(DP.Document_ID) as MyCount, Count(*) OVER() as MyTotal
2012 express
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
Same error
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

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
Change line 9 to

Select Project, MyCount, sum(MyCount) OVER(Order by Project ROWS UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as MyTotal
Avatar of Vitor Montalvão
What's the difference of this question with this one http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_28554552.html?
It says Incorrect syntax near the keyword 'AND'.
To whom are you addressing your comments? My code does not use the word "AND".
Philip,

This line:

Select Project, MyCount, sum(MyCount) OVER(Order by Project ROWS UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as MyTotal
ASKER CERTIFIED SOLUTION
Avatar of Phillip Burton
Phillip Burton

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
YES!!!!!! Thank you!
ty