Click here to Skip to main content
13,259,194 members (51,523 online)
Rate this:
 
Please Sign up or sign in to vote.
See more:
Hi

I have two tables, customers and numbers.

numbers contains a 'customer' field which corresponds to customers.id; the customer who owns the number.

I would like to return a list of customers who have at least one number, and how many numbers they own.

I've tried the following query but it's giving an error:

select c.name as "Customer", count(*) as "Numbers" from customers c, numbers n where n.customer=c.id and (select count(*) from numbers where customer=c.id) > 0


What I have tried:

---------------------
In question
---------------------
Posted 10-Nov-17 2:22am
AntiRix298
Updated 10-Nov-17 2:42am
v2
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

SELECT MAX(c.name) AS "Customer",COUNT(n.customer) as "Numbers" 
   FROM 
  customers c INNER JOIN numbers n 
     on(n.customer=c.id) 
   GROUP BY c.id
     HAVING count(n.customer)>0
---------------------------------------
SELECT c.name  AS "Customer",Numbers 
   FROM (SELECT ID,Name from customers)AS c
   INNER JOIN (
  SELECT customer,Counr(1) AS "Numbers" from numbers GROUP BY customer)n 
     ON(n.customer=c.id) 
         WHERE Numbers<>0 
  Permalink  
v2
Comments
AntiRix 10-Nov-17 7:51am
   
Perfect, that works! Thank you c:
Santosh kumar Pithani 10-Nov-17 7:52am
   
Welcome AntiRix
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy |
Web02 | 2.8.171114.1 | Last Updated 10 Nov 2017
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100