CODEWITHSUNDEEP
mysqlsql
user3681491
user3681491

Reputation:

select query to group items but also limit

i am running this sql query:

SELECT * FROM customer_billing a WHERE 

(SELECT COUNT(*) FROM customer_billing b WHERE 
 a.producttype = 'VoIP Telephony' and 
 a.productname = 'VoIP Geographic Number (01/02)' and 
 a.customer_seq = b.customer_seq) 
 <= 1 order by customer_seq ASC

it should only show rows where producttype = 'VoIP Telephony' but its showing others two

where producttype = 'Phone Lines' and more

Upvotes: 0

Views: 27

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

I think you are trying to do this:

SELECT *
FROM customer_billing cb
WHERE cb.producttype = 'VoIP Telephony' and 
      cb.productname = 'VoIP Geographic Number (01/02)' and
      (SELECT COUNT(*)
       FROM customer_billing cb2 
       WHERE cb2.producttype = cb.producttype and 
             cb2.productname = cb.productname and
             cb2.customer_seq = cb.customer_seq
     ) <= 1
order by customer_seq ASC;

Your version of the query is doing the comparisons inside the subquery. So they only apply to the count(*) and not to the filtering of the entire query.

Upvotes: 1

jcaron
jcaron

Reputation: 17710

What you are doing is selecting the customers that have only 1 line in the table that match the producttype and productname given. But the customers can have more lines.

One solution is simply to repeat those two conditions on the "outer" SELECT:

SELECT *
  FROM customer_billing a
  WHERE 
  (
    SELECT COUNT(*)
      FROM customer_billing b
      WHERE 
        a.producttype = 'VoIP Telephony' AND 
        a.productname = 'VoIP Geographic Number (01/02)' AND
        a.customer_seq = b.customer_seq
  ) <= 1 AND 
  a.producttype = 'VoIP Telephony' AND
  a.productname = 'VoIP Geographic Number (01/02)'
  ORDERY BY customer_seq ASC

Upvotes: 0

Related Questions