Solved

** How to add Row Count to Each Record in SQL

Posted on 2014-04-24
12
724 Views
Last Modified: 2014-04-25
Hi,

In the same select statement, I'd like a column that displays the TOTAL row count for the results.  

For example:

SELECT Order_ID, Date, Tot_Rows
FROM Table1

result:

Order_ID      Date        Tot_Rows
10               1/1/13           5
12               1/12/13         5
13                3/12/13        5
14               3/23/13         5
24                4/5/13          5

Can this be accomplished in my SELECT statement?

Thank you!
0
Comment
Question by:dunkin1969
[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
  • 4
  • 4
  • 2
  • +2
12 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40021299
Try this:
SELECT Order_ID, Date, count(*) over() Tot_Rows
FROM Table1

Should work in SQL Server and Oracle.
0
 

Author Comment

by:dunkin1969
ID: 40021350
Thanks slightwv !! Works perfectly.

Additionally, when I add a GROUP BY clause to the statement, TOT_ROWS works as intended and returns the "grouped" number of rows (125).  In reality, I would like TOT_ROWS to display for total rows (1500).  Can your code be adjusted to count all rows in the detail, instead of the # rows returned by the GROUP BY?

I hope I made myself clear enough for you.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40021438
, COUNT(*) OVER(PARTITION BY 1) AS Tot_Rows

That will work in SQL Server, can't say for sure about Oracle, but I presume it should work.
0
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40021466
What database is this for?  It was asked in SQL Server and Oracle.

>>can't say for sure about Oracle, but I presume it should work.

Doesn't work like that in Oracle.  You need to specify the actual column name that you want to partition by.

>>Can your code be adjusted to count all rows in the detail, instead of the # rows returned by the GROUP BY?

I'm not understanding what you want.

Can you provide more sample data if necessary but some expected results and maybe an updated select that shows your grouping?
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40021550
>> Doesn't work like that in Oracle.  You need to specify the actual column name that you want to partition by. <<

Could be.  But, just be clear, the 1 is not intended as a relative column#, as in an ORDER BY, but as a static value, so that the count is of all rows, as desired.
0
 

Author Comment

by:dunkin1969
ID: 40021561
Slightwv,

Your code was successful in doing this: (thank you, that was the correct answer to my original question)

Order_ID      Date        Tot_Rows
10               1/1/13           9
10               1/12/13         9
10                3/12/13        9
10                3/14/13        9
14               3/23/13         9
14                4/5/13          9
14               3/12/13         9
14               3/23/13         9
14                4/5/13          8

But now what if I did GROUPING on the Order_ID , and I want to produce this:

Order_ID      Count_Orders    Tot_Row
10                       4                      9
14                       5                      9

Your code as is would produce this:

Order_ID      Count_Orders    Tot_Row
10                       4                      2
14                       5                      2

What can I do to get it to show "9" as Tot_Row (the total # of records)

Thanks!

Oh, ScottPletcher... your code couldn't produce the "9" either.

Thanks guys!!!
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 40021591
Database type and version would help a lot.

I can think of two ways off the top of my head.  There may be more and I can't say which one is better with a small test case.

I think this is also SQL Server syntax but I'm an Oracle guy.  Scott is a SQL Server guy so one of us can help once we know your specific database.

SELECT distinct Order_ID,
	count(*) over(partition by order_id) count_orders,
	count(*) over() tot_row
FROM tab1
/

select order_id, count_orders, tot_row from (
select Order_ID,
	count(*) over(partition by order_id) count_orders,
	count(*) over() tot_row,
	row_number() over(partition by order_id order by order_id) rn
from tab1
)
where rn=1
/

Open in new window

0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 total points
ID: 40021598
Another way:

select order_id, count_orders, sum(count_orders) over() tot_row
from
(
SELECT Order_ID, count(*) count_orders
FROM tab1
GROUP BY order_id
)
/

Open in new window

0
 

Author Comment

by:dunkin1969
ID: 40021645
Thank you for the quick response Slightwv.  I will review tomorrow morning.

Also, I'm using SQL Server, but also have access to Oracle as well.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40022524
I have an article out there called SQL Server GROUP BY Solutions that would be a good read.  Point 5 is a demo on Aggregate AND values from a single row that make up the aggregate.

>I'd like a column that displays the TOTAL row count for the results.  
For the record, slightwv answered your original question as written, and it looks like you're asking a bunch of follow-up questions.  It would help if you can state your entire question upfront, so experts can be more efficient with their time helping you.
0
 
LVL 32

Expert Comment

by:awking00
ID: 40022563
Just an observation (no points, please) -  The Oracle solutions provided by slightwv should work in SQL Server as well except that SQL Server will require you to provide an alias for the sub-queries.
0
 

Author Comment

by:dunkin1969
ID: 40023989
Thanks SlightWV, all versions of your codes work like a charm!!!
 And great article Jim!!

thanks to all who participated in helping me to a solution!!!  Much appreciation to this community!!
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

739 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