I am writing a query like this...
select consumer_name,count(select * from consumer where product='TTT')
from consumer;
I want filtered count... What will be perfect query for this?
4 Answers
Maybe something like this:
select
consumer_name,
(
SELECT
COUNT(*)
FROM
consumer AS tbl
WHERE
tbl.product='TTT'
)
from
consumer;
Comments
Try
select
consumer_name, COUNT(your_column_name which you want to count)
from consumer
WHERE tbl.product='TTT' group by colusumer_id;
1 Comment
Frank Schmitt
You're missing a GROUP BY (unless I'm missing something).
You can use SUM instead of COUNT and insert the logic by CASE or DECODE
The DECODE one
SELECT consumer_name, SUM(DECODE(product, 'TTT', 1, 0))
FROM consumer;
The CASE one
SELECT consumer_name, SUM(CASE WHEN product = 'TTT' THEN 1 ELSE 0 END))
FROM consumer;
Comments
if you are asking to hide count ( and whatever in your output ) you can use alias :
select consumer_name,
count(select * from consumer where product='TTT') as A /*or any thing that you want */
from consumer;
or
select
consumer_name,
(
SELECT
COUNT(*)
FROM
consumer AS tbl
WHERE
tbl.product='TTT'
) as A
from
consumer;
COUNT()have no relation to the rest of the rows.