Menu
I'm trying to construct the correct sql statement (Oracle) to get the count of device_id's for each customer_id that is greater than a given value. For example, I want to know the customer_id's that have more than 3 device_ids. A single device_id can only have one customer_id associated to it, while a customer_id may have many device_ids.
Table:
device_id
customer_id
....
Data (device_id, customer_id):
00001, CUST1
00002, CUST1
00003, CUST1
00004, CUST1
00005, CUST2
00006, CUST2
232
SQL COUNT( ) with where clauseThe WHERE clause can be used along with SQL COUNT() function to select specific records from a table against a given condition.
SELECT COUNT(*) FROM table_name; The COUNT(*) function will return the total number of items in that group including NULL values. The FROM clause in SQL specifies which table we want to list. You can also use the ALL keyword in the COUNT function.
You need table aliases. So I think you want: Select ID as Num, (select Count(*) from TASK t where t.ID = o.ID) as Total from ORDER o; By the way, ORDER is a terrible name for a table because it is a reserved work in SQL.
COUNT(*) returns the number of rows in a specified table, and it preserves duplicate rows. It counts each row separately. This includes rows that contain null values.
To get the customers with more than 3 devices:
select customer_id, count(device_id)
from YourTable
Group by customer_id
having count(device_id) > 3
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
