This example is use Northwind database in SQL Server.
Contoh perintah SQL berikut menggunakan database Northwind di SQL Server.
This query is to display grand total each order and the description of grand total according to sales target which is $500.
Query ini menampilkan grand total dari setiap order dan keterangan menurut target penjualan yaitu sebesar $500.
The query :
Query-nya :
select a.orderid,
c.firstname + ' ' + c.lastname as employeename,
sum(b.unitprice*b.quantity) as subtotal,
description =
case
when sum(b.unitprice*b.quantity) > 500 then 'over target'
when sum(b.unitprice*b.quantity) < 500 then 'under target'
when sum(b.unitprice*b.quantity) = 500 then 'on target'
else 'n/a'
end
from orders a
inner join [order details] b on a.orderid = b.orderid
inner join employees c on a.employeeid = c.employeeid
group by a.orderid, c.firstname + ' ' + c.lastname
order by a.orderid
Modification the query, for example; we want to know grand total each employee on August 1994 :
Modifikasi query untuk mengetahui grand total setiap pegawai pada bulan Agustus 1994 :
select c.firstname + ' ' + c.lastname as employeename,
sum(b.unitprice*b.quantity) as subtotal,
description =
case
when sum(b.unitprice*b.quantity) > 5000 then 'over target'
when sum(b.unitprice*b.quantity) < 5000 then 'under target'
when sum(b.unitprice*b.quantity) = 5000 then 'on target'
else 'n/a'
end
from orders a
inner join [order details] b on a.orderid = b.orderid
inner join employees c on a.employeeid = c.employeeid
where month(a.orderdate) = 8 and year(a.orderdate) = 1994
group by c.firstname + ' ' + c.lastname
order by c.firstname + ' ' + c.lastname
Happy learning! Selamat belajar!
Update Contact :
No Wa/Telepon (puat) : 085267792168
No Wa/Telepon (fajar) : 085369237896
Email : Fajarudinsidik@gmail.com
No Wa/Telepon (puat) : 085267792168
No Wa/Telepon (fajar) : 085369237896
Email: Fajarudinsidik@gmail.com
atau Kirimkan Private messanger melalui email dengan klik tombol order dibawah ini :
