[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 739
  • Last Modified:

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

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
dunkin1969
Asked:
dunkin1969
  • 4
  • 4
  • 2
  • +2
2 Solutions
 
slightwv (䄆 Netminder) Commented:
Try this:
SELECT Order_ID, Date, count(*) over() Tot_Rows
FROM Table1

Should work in SQL Server and Oracle.
0
 
dunkin1969Author Commented:
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
 
Scott PletcherSenior DBACommented:
, 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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
slightwv (䄆 Netminder) Commented:
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
 
Scott PletcherSenior DBACommented:
>> 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
 
dunkin1969Author Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
dunkin1969Author Commented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
awking00Commented:
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
 
dunkin1969Author Commented:
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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 4
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now