CODEWITHSUNDEEP
sql
user
user

Reputation: 1

Sql Query: How to Base on the row name to display

I have the table data as listed on below:

name  | score
andy  | 1
leon  | 2
aaron | 3

I want to list out as below, even no jacky's data, but list his name and score set to 0

aaron    3
andy     2
jacky    0
leon     2

Upvotes: 0

Views: 200

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

Your question lacks a bunch of information, such as where "Jacky"s name comes from. If you have a list of names that you know are not in the table, just use union all:

select name, score
from t
union all
select 'Jacky', 0;

Upvotes: 0

user
user

Reputation: 1

fixed the query, could list out the data, but still missing jacky, only could list out as shown on below, the DBMS. In SQL is SQL2008.

data name score scoredate andy 1 2021-08-10 01:23:16 leon 2 2021-08-10 03:25:16 aaron 3 2021-08-10 06:25:16 andy 4 2021-08-10 11:25:16 leon 5 2021-08-10 13:25:16

result set name | score aaron | 1 andy | 5 leon | 7

select v.name as Name,
coalesce(sum(t.score),0) as Score 
from (
values ('aaron'), ('andy'), ('jacky'), ('leon')
) as v(name)
left join Score t on t.name=v.name
where scoredate>='2021-08-10  00:00:00'
and scoredate<='2021-08-10  23:59:59'
group by v.name
order by v.name asc

Upvotes: 0

user330315
user330315

Reputation:

You didn't specify your DBMS, but the following is 100% standard ANSI SQL:

select v.name, coalesce(t.score, 0) as score
from (
  values ('andy'),('leon'),('aaron'),('jacky')
) as v(name)
  left join your_table t on t.name = v.name;

The values clause builds up a "virtual table" that contains the names you are interested in. Then this is used in a left join so that all names from the virtual table are returned plus the existing scores from your (unnamed table). For non-existing scores, NULL is returned which is turned to 0 using coalesce()

If you only want to specify the missing names, you can use a UNION in the virtual table:

select v.name, coalesce(t.score, 0) as score
from (
  select t1.name
  from your_table t1
  union 
  select * 
  from ( values ('jacky')) as x
) as v(name)
  left join your_table t on t.name = v.name;

Upvotes: 2

Related Questions