Displaying a count of zero.

[basildon]

I have a database with details about many of the Worlds big companies (e.g. Turnover, Number of Employees etc.).

If I wished to count the number of companies in each country with over 10000 employees I would use

SELECT
country,
COUNT(*)
from TABLE1
where (employee_total > 10000)
GROUP BY country
;

Which is fine. However this doesn't display the countries which count zero (i.e. countries on the database with no companies larger than 10000 employees)

I've had a look around, but don't seem to be able to find a solution. Any ideas?

 

[bperry]

Hi there,
Not sure what you are getting at here, need to gather a bit more information.
Your first query (which works) counts companies > 10,000 employee, by country.

Then I take it you go on to say that instead of companies with >10000 employees, you instead want companies with 0 employees:

SELECT
country,
COUNT(*)
from TABLE1
where (employee_total = 0)
GROUP BY country

And this doesn't work? (Although I think it should).Is that it?

Does this summarize your problem, or maybe I have it wrong. Any further comments or clarification might help.

bperry

 

[PruSQLer]

I managed to do that using a temporary table. It's actually called a table expression in DB2. If you can use temp tables in your RDBMS, this will work. If not, sorry about that.

with temp_table
(country, LT, GT)
As (
select country,
case when employees <= 10000 then 1 else 0 end,
case when employees > 10000 then 1 else 0 end
from TABLE1
)
Select country,
sum(LT),
sum(GT)
From temp_table
Group by country

 

[basildon]

Hi bp,

What I'm looking for is for every country to return a result, even if that result is zero.

With the query I posted earlier, if a count was zero neither the country nor the count (zero) was displayed.

As an example, if I were to ask for a count by country on companies with 10000+ employees I'd get something like this...

+--------------------+----------+
| country | COUNT(*) |
+--------------------+----------+
| ARGENTINA | 3 |
| AUSTRALIA | 10 |

What I want is something like this...

+--------------------+----------+
| pcountry | COUNT(*) |
+--------------------+----------+
| ALGERIA | 0 |
| ARGENTINA | 3 |
| AUSTRALIA | 10 |
| AUSTRIA | 0 |

Thanks again for you help

 

[bperry]

Would you like to try this:

Select
t1.Country,
(Select COUNT(*) from Table1 t2
Where t2.Country = t1.Country
and employee_total > 10000) as TheCount
from Table1 t1
group by t1.Country


--bperry

 

[karluk]

One way of doing this is with the following query. Note the union in the inner query, which ensures that you will get at least one row for every country in your table.

SELECT COUNTRY, MAX(LARGE_COMPANYS) FROM
(SELECT COUNTRY, COUNT(EMPLOYEE_TOTAL) AS LARGE_COMPANYS
FROM TABLE1
WHERE EMPLOYEE_TOTAL > 10000
GROUP BY COUNTRY
UNION
SELECT DISTINCT COUNTRY, 0 AS LARGE_COMPANYS
FROM TABLE1)
GROUP BY COUNTRY

 

[crufty]

More than one way to write a query. The query below uses some Oracle specific functions:

(+) -> is used for the inner join
nvl(column, n) -> if the column returns a NULL, substitute in the number n else use the value in column

select t0.country, nvl(t1.number, 0)
from
(select distinct country
from table 1) t0,
(SELECT
country,
COUNT(*) number
from TABLE1
where (employee_total > 10000)
GROUP BY country) t1
where t0.country = t1.country(+) ;

 

[basildon]

Thanks to all for you help.