CODEWITHSUNDEEP
sqloracle-database
pratteek shaurya
pratteek shaurya

Reputation: 960

Print message when no data is found

Need a query to get the Employee name, total fuel used by each employee. If fuel is not used by an employee then the second column should have a text “No fuel used”.

These are the following two tables:

Table1: EmployeeID, FirstName

1 Vikas
2 nikita
3 Ashish
4 Nikhil
5 anish

Table2: ID, Fuel

1 10
2 9
3 8
4 6
5 12
6 11
7 10
8 9
9 8
10 10
11 9
12 12
13 7
14 15

where The column table2.ID is a foreign key to table1.EmployeeID.

This is code which I have written, Which is most probably wrong.

select ID, FirstName, sum(table2.Fuel) sum_fuel
from table2,table1
where EmployeeID=ID IN (
select ID, coalesce(ID, 'No-fuel used') as ID
from table1 t1
left join table2 t2 on t2.ID = t1.EmployeeID
)
group by fuel
order by ID DESC;

As you can see from two tables that employee with from 1 to 5 of table1 are in table2. So for these employee I need to show total fuel used by every individual. And for employee with ID from 6 to 14 are not available in table1 so for these employee “No fuel used” message should be printed.

Upvotes: 0

Views: 94

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65228

You can use a left join. This way, whenever the Id values for tables don't match you'll get null values for sum(fuel) value, and will assign the string 'No fuel used'for sum_fuel column by using nvl() function:

with table1( EmployeeID, FirstName ) as
(
 select 1,'Vikas'  from dual union all 
 select 2,'nikita' from dual union all 
 select 3,'Ashish' from dual union all 
 select 4,'Nikhil' from dual union all 
 select 5,'anish'  from dual union all 
 select 15,'pratteek'  from dual 
), table2( ID, Fuel ) as
(
 select 1, 10  from dual union all
 select 2, 9   from dual union all
 select 3, 8   from dual union all
 select 4, 6   from dual union all
 select 5, 12  from dual union all
 select 6, 11  from dual union all
 select 7, 10  from dual union all
 select 8, 9   from dual union all
 select 9, 8   from dual union all
 select 10, 10 from dual union all
 select 11, 9  from dual union all
 select 12, 12 from dual union all
 select 13, 7  from dual union all
 select 14, 15 from dual       
)
select EmployeeID, FirstName, nvl(to_char(sum(t2.Fuel)),'No fuel used') as sum_fuel 
  from table1 t1
  left join table2 t2 
    on t1.EmployeeID = t2.ID 
 group by EmployeeID, FirstName
 order by EmployeeID desc;

 EMPLOYEEID FIRSTNAME   SUM_FUEL
 ---------- ---------   ------------
 15         pratteek    No fuel used
  5         anish       12
  4         Nikhil      6
  3         Ashish      8
  2         nikita      9
  1         Vikas       10

Demo

Upvotes: 1

Khairul Alam
Khairul Alam

Reputation: 1336

This may work---

SELECT ID
     , FirstName
     , CASE 
               WHEN SUM(f.Fuel) > 0 THEN  CAST(SUM(f.Fuel)  AS NVARCHAR(25))
               ELSE 'No fuel used'
      END sum_fuel

FROM #emp e
LEFT JOIN #fuel f ON e.EmployeeID = f.id
GROUP BY ID,FirstName
ORDER BY ID DESC

Upvotes: 0

Related Questions