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
Microsoft SQL ServerMicrosoft SQL Server 2008SQL

Avatar of undefined
Last Comment
vbnetcoder

8/22/2022 - Mon
ASKER
vbnetcoder

Actually the total column should say 9 ...dah!  meaning the sum of all the counts
Phillip Burton

add at the end of line 1

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

Open in new window

ASKER
vbnetcoder

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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Phillip Burton

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.
ASKER
vbnetcoder

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.
Phillip Burton

Which version of SQL Server do you have? 2008?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Phillip Burton

Try this instead as the first line:

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

2012 express
Phillip Burton

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
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER
vbnetcoder

Same error
Phillip Burton

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

ASKER
vbnetcoder

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Phillip Burton

Change line 9 to

Select Project, MyCount, sum(MyCount) OVER(Order by Project ROWS UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as MyTotal
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?
ASKER
vbnetcoder

It says Incorrect syntax near the keyword 'AND'.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Phillip Burton

To whom are you addressing your comments? My code does not use the word "AND".
ASKER
vbnetcoder

Philip,

This line:

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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
vbnetcoder

YES!!!!!! Thank you!
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
vbnetcoder

ty