How to Display an output with a value 0-CodePudding

I have a query where it returns the number of tickets resolved by a member. However, if a member has done 0 tickets he/she is not displayed in the output. Is there a way to let the query return that value to zero instead of removing it?

Example:

select loginname,
       count(id) as total_tickets
from tickets
where loginname in ("x", "y", "z", "s", "a")

Output was like this:

I know s has a value of 0 but I want to make it visible. Any idea? Thanks

CodePudding user response：

You didn't specify your DBMS, but in standard ANSI SQL, you can do an outer join against a list of values:

select v.name,
       count(t.id) as total_tickets
from (
  values ('x'), ('y'), ('z'), ('s'), ('a') 
) as v(name)  
  left join tickets t on t.loginname = v.name
group by v.name;

Note that string values need to be enclosed in single quotes in standard SQL (double quotes are for identifiers).

If your DBMS doesn't support the VALUES clause like that, you can e.g. use a union to build up the list.

CodePudding user response：

Your query is invalid. It is missing the GROUP BY clause. You are also using double quotes where it should be single quotes.

You are looking for an outer join. Select from members, join the tickets or ticket counts.

With a join before aggregation:

select
  m.loginname,
  count(id) as total_tickets
from members m
left outer join tickets t on t.loginname = m.loginname
where m.loginname in ('x', 'y', 'z', 's', 'a')
group by m.loginname
order by m.loginname;

With a join after aggregation (which I usually prefer):

select
  m.loginname,
  t.total_tickets
from members m
left outer join 
(
  select loginname, count(*) as total_tickets
  from tickets
  group by loginname
) t on t.loginname = m.loginname
where m.loginname in ('x', 'y', 'z', 's', 'a')
order by m.loginname;