Why one column is always has zero when we use two COUNT requests?-CodePudding

I'm trying to get table with 3 columns: 1- year, 2- quantity of foreign cars, 3- quantity of Russian cars (grouped by year). Values from the table having columns: 1- year, 2- type, 3- id application. Result is always the same - one of the column with quantity is 0 or both of columns has the same values, another words, it is wrong . Where is the mistake? Result source table I was trying different ways:

SELECT autotable.year as Year, 
       count(CASE WHEN autotable.Type='Foreign' THEN 1 ELSE null END) as Quantity_Foreign_Cars, 
       count(CASE WHEN autotable.Type= 'Russia' THEN 1 ELSE null END) as Quantity_Russian_Cars 
FROM banktest.autotable 
group by autotable.Year;

SELECT autotable.year as Year, 
       sum(IF (autotable.Type='Foreign',1,0)) as Foreign_Cars,
       sum(IF (autotable.Type ='Russia',1,0)) as Russian_Cars 
FROM banktest.autotable 
group by autotable.Year;

SELECT autotable.year as Year, 
       (SELECT count(autotable.Type) 
        FROM banktest.autotable 
        where autotable.Type='Foreign') as Foreign_Cars,
       (SELECT count(autotable.Type) 
        FROM banktest.autotable 
        having autotable.Type='Russia') as Russian_Cars 
FROM banktest.autotable 
group by autotable.Year;

CodePudding user response：

So as far as i understand, you are having a table with 2 columns. Year and Type, right? You need to group by both of your columns and use the count method.

If you are using a oracle database this should do the trick:

select count(*), trunc(year,'Y'), type 
from autotable
group by 
trunc(year,'Y'), type;

CodePudding user response：

This will do what you want:

select count(case when type = 'Foreign' then 1 end) as foreign, count(case 
when type = 'Russia' then 1 end) as russian, trunc(year,'Y')
from autotable
group by 
trunc(year,'Y')