Getting a 0 count 2

[webmigit]

I have a query, which essentially is this...

select count(visible) as cVis, Visible
from products

Visible can have a value of 0 or 1.. and I'm trying to figure out how to always get a count on both (Invisible 0 and Visible 1).

Right now if there's no count for one of them, it doesn't return it.

Any help is appreciated.

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[sleipnir214]

Do you want to know how many times each possible value for the column "visible" appears?

Something like:

select visible, count(visible) as value_count from foo group by visible

will return two rows, with one column being the value and the other column being the number of times that value appears.

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[webmigit]

I forgot that mine has the group by clause, but it does...

Say of 110 products, there are 22 visible (1) and 88 invisible (0).. I'd want to return.

cVis | Visible
88   | 0
22   | 1

Which is what I get, but say, all 110 are invisble, all I get is..

cVis | Visible
110  | 0

What I'd like is..

cVis | Visible
110  | 0
[red]0    | 1[/red]

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[sleipnir214]

I dunno. I've always just programmed my apps to treat the absence of a value a a zero-count.

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[TonyGroves]

You could use:
[tt]
SELECT COUNT(*) cvis,0 visible
FROM products
WHERE visible=0
UNION ALL
SELECT COUNT(*),1
FROM products
WHERE visible=1
[/tt]

 

[r937]

tony, if there are no rows in the table with visible=1, then that subselect in your UNION is going to return no rows

here's how you do it for all situations --

select count(p.visible) as cVis
     , v.visible
  from (
       select 0 as visible
       union all
       select 1
       ) as v
left outer
  join products as p
    on v.visible = p.visible
group
    by v.visible

r937.com | rudy.ca

 

[TonyGroves]

You're talking rubbish, Rudy. My query will work, and it's a lot clear and simpler than yours. Have you tried it?

 

[r937]

sorry, man, momentary brain cramp

r937.com | rudy.ca

 

[webmigit]

Thanks guys

The thing is that this query uses a lot of unions as it is...

Its a reporting query for a system that I don't understand very well yet, to be honest. Its only intended to run once every few days or so, during off peak hours.

Wouldn't the above method double the unions and cause double the load? Sorry I didn't state this before.

Again, thank you. You did answer the question I asked.

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[TonyGroves]

Sure, it increases your union count, but if it does what you want ...

You have two different solutions here. You could try them both and see which suits best.