Solved

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

Posted on 2014-04-24
12
705 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)
Comment Utility
Try this:
SELECT Order_ID, Date, count(*) over() Tot_Rows
FROM Table1

Should work in SQL Server and Oracle.
0
 

Author Comment

by:dunkin1969
Comment Utility
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:ScottPletcher
Comment Utility
, 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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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:ScottPletcher
Comment Utility
>> 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
Comment Utility
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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 31

Expert Comment

by:awking00
Comment Utility
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
Comment Utility
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to recover a database from a user managed backup
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

763 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now