Solved

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

Posted on 2014-04-24
12
713 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 76

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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 76

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 76

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 76

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
t-sql Joining Issue 10 38
best counters for cpu high usage 3 30
SQL Insert parts by customer 12 33
Find results from sql within a time span 11 30
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
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.

773 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