Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-04-24
12
Medium Priority
?
735 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
  • 4
  • 4
  • 2
  • +2
12 Comments
 
LVL 78

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 70

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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 78

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 70

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 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 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 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 2000 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 66

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

824 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