CODEWITHSUNDEEP
sqlsql-servert-sql
Greatran
Greatran

Reputation: 190

Counting rows with condition

My Table looks like something below

Id | Customer_number | Customer_Name | Customer_owner

I want to insert Customer_Number as a sequence specific to Customer_owner

that is 1,2,3,.... for Customer_owner X and 1,2,3,... Customer_owner Y.

To get the Customer_number I can use following SQL

SELECT COUNT(*) FROM Customer where Customer_owner='X'

My question is that are there any performance impact. Specially for a table with 100,000 records. Are there any better alternatives?

Upvotes: 0

Views: 97

Answers (3)

Vlad G.
Vlad G.

Reputation: 2147

If you only need unique numbering in the UI you could just assign the numbers in the UI. If you go that route you need to make sure you always retrieve customers in the same order, so add an ORDER BY Id, Or, do what Gordon Linoff suggests.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You can calculate Customer_Number on the fly when you need it:

select c.*, row_number() over (partition by Customer_Owner order by id) as CustomerNumber
from Customer c

This is a much safer approach than trying to store and maintain the number, which can be affected by all sorts of updates into the system. Imagine the fun of changing the numbering when an existing record changes it ownership, for instance.

Upvotes: 1

Ron.B.I
Ron.B.I

Reputation: 2766

In terms of performance, I would suggest not adding another column to Customers, for various reasons:

  1. The need to update all of owner's A related customers when adding a customer with the owner A, same goes for removing.

  2. Number of clients is Repeated multiple times - taking up more space and thus (generally) slowing execution.

  3. No real usage to link Number of clients to client's owner via another column for a record describing a single customer.

and many more explanations..

The correct normal form would be having 2 tables:

  1. Customers(Cust_id,Cust_name,Cust_Owner_id)

2.a. Owners (Owner_id,Owner_name,NumberOfCustomers)

OR

2.b. Owners (Owner_id,Owner_name) and have NumberOfCustomers be auto calculated upon Querying.

Edit: Since you want to display all the customers for a single owner, I assume that is your main usage, you should add a cluster index on Cust_Owner_id . Then , when querying, performance would be good since it will have the benefits of clustering according to your desired data.

Read more about clustering here: Clustered Index

Edit 2:

I've just realized your intent via latest comments, but the solution still remains, I would add, specific to your issue, that I don't recommend you should store the number for all of one owner's customers, instead, keep a SUBSCRIBED DATE Column in Customers table, and when querying, decide of the customer number upon display. If you want however that number to be permanent (any by that the order 1,2,3,..n will probably break, since customers can be removed), simply use the Customer_Id, since it is already unique.

Upvotes: 1

Related Questions