Sql - Count table query error

Question

I have a query where I want to display the owner and the number of times they took the car, bus or train to work. so the table should look like this;

Owner | Car | Bus | Train
-------------------------
Joe   | 1   | 2   | 4

This is my query;

Select owner, vehicle
From MyTable
    INNER JOIN(select  
  count(case when vehicle = 'Car' then 1 else 0 end) AS [Car],
  count(case when vehicle = 'Bus' then 1 else 0 end) AS [Bus],
  count(case when vehicle = 'Train' then 1 else 0 end) AS [Train]
from dbo.MyTable
where
     YEAR([CreatedOn]) = 2015
group by 
    vehicle)

Im getting an incorrect syntax error

Gordon Linoff · Accepted Answer · 2015-11-10 23:49:22Z

2

First, use sum() rather than count(). Second, you don't need a subquery. Third, you need to group by owner, not vehicle:

Select owner,
       sum(case when vehicle = 'Car' then 1 else 0 end) AS [Car],
       sum(case when vehicle = 'Bus' then 1 else 0 end) AS [Bus],
       sum(case when vehicle = 'Train' then 1 else 0 end) AS [Train]
From MyTable
where YEAR([CreatedOn]) = 2015
group by owner;

You can use count(), but it counts non-NULL values, so it is misleading. In your case, for instance, the conditional logic is ignored.

EDIT:

To get the rest, use the same idea, just change the condition:

       sum(case when vehicle not in ('Car', 'Bus', 'Train') then 1 else 0 end) AS Others

edited Nov 10, 2015 at 23:49

answered Nov 10, 2015 at 15:14

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

AndroidAL Over a year ago

thanks, how could I count the rest that is not car,bus,train?

D'Arcy Rittich · Accepted Answer · 2015-11-10 15:14:06Z

1

Try this:

select owner
    ,count(case when vehicle = 'Car' then 1 end) as [Car]
    ,count(case when vehicle = 'Bus' then 1 end) as [Bus]
    ,count(case when vehicle = 'Train' then 1 end) as [Train]
from dbo.MyTable
where year([CreatedOn]) = 2015
group by owner

answered Nov 10, 2015 at 15:14

D'Arcy Rittich

172k41 gold badges298 silver badges287 bronze badges

Comments

Lukasz Szozda · Accepted Answer · 2015-11-10 18:22:04Z

1

You cannot use ELSE 0 becuase it will be counted. Use ELSE NULL or leave default.

Select owner,
     count(case when vehicle = 'Car' then 1 end) AS [Car],
     count(case when vehicle = 'Bus' then 1 end) AS [Bus],
     count(case when vehicle = 'Train' then 1 end) AS [Train],
     count(case when vehicle NOT IN ('Car', 'Bus', 'Train') 
                OR vehicle IS NULL THEN 1 END) AS [Others]
from dbo.MyTable
where YEAR([CreatedOn]) = 2015
group by owner;

edited Nov 10, 2015 at 18:22

answered Nov 10, 2015 at 15:14

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

1 Comment

Lukasz Szozda Over a year ago

@AndroidAL thanks, how could I count the rest that is not car,bus,train See updated

Collectives™ on Stack Overflow

Sql - Count table query error

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related