• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

Kumpulan Kueri MySQL

Kepala Suku

Web & Mobile Developer
Staff member
#1
Kuery untuk melihat detail potongan harga rentang tertentu:
SQL:
SELECT u.name customer, c.name, i.promo_harga potongan, date(i.created_at) tanggal_transaksi FROM `invoices` i 
left JOIN companies c on i.company_id=c.id
left join users u on i.user_id=u.id
WHERE i.created_at >= '2020-01-31 00:00:00' and i.created_at < '2020-02-8 00:00:00'
and i.status ='paid' and i.promo_harga!=0
order by potongan desc
Kuery untuk melihat jumlah potongan harga rentang tertentu:
SQL:
SELECT c.name, sum(i.promo_harga) total FROM `invoices` i
left JOIN companies c on i.company_id=c.id
WHERE i.created_at >= '2020-01-31 00:00:00' and i.created_at < '2020-02-8 00:00:00'
group BY i.company_id
ORDER by total desc
 

Kepala Suku

Web & Mobile Developer
Staff member
#2
Melihat jumlah transaksi masing-masing perusahaan pada rentang tertentu:
SQL:
SELECT c.name, sum(i.paid) transaksi FROM `invoices`  i
LEFT JOIN companies c on i.company_id=c.id
WHERE date(i.created_at)>= "2020-02-01" and i.company_id!=17 GROUP by i.company_id
ORDER by transaksi desc
 

Kepala Suku

Web & Mobile Developer
Staff member
#3
Melihat jumlah pendaftar pada rentang tanggal tertentu:
SQL:
SELECT date(created_at) tanggal, count(id) jumlah_pendaftar FROM `users` WHERE date(created_at) >= "2020-02-01" group BY date(created_at)
 

Kepala Suku

Web & Mobile Developer
Staff member
#4
Melihat jumlah detail transaksi per bulan order by nama

SQL:
SELECT date_format(i.created_at, "%d %M %Y") tanggal_transaksi, c.name, ci.name kota, i.customer_name, i.promo_harga potongan, i.total total_harga  FROM `invoices` i
LEFT JOIN companies c on i.company_id=c.id
LEFT JOIN cities ci on c.city_id=ci.id
WHERE i.status='paid' and month(i.created_at) = 04 and year(i.created_at) = 2020
ORDER by c.name asc
 

Kepala Suku

Web & Mobile Developer
Staff member
#5
Melihat daftar perusahaan yang belum registered
SQL:
SELECT c.name nama_bengkel, u.name owner_bengkel, u.mobile nomor_telpon, date_format(c.created_at, "%d %M %Y") tanggal_daftar FROM `companies` c
LEFT JOIN users u on c.user_id=u.id
WHERE aproved=0
ORDER BY c.created_at ASC
 

Kepala Suku

Web & Mobile Developer
Staff member
#6
Melihat Daftar Transaksi Rentang Tertentu
SQL:
SELECT date_format(i.created_at, "%d %M %Y") tanggal_transaksi, c.name, ci.name kota, i.customer_name, i.promo_harga potongan, i.total total_harga  FROM `invoices` i
LEFT JOIN companies c on i.company_id=c.id
LEFT JOIN cities ci on c.city_id=ci.id
WHERE i.status='paid' and date(i.created_at) BETWEEN '2020-05-09' AND '2020-05-16'
ORDER by i.created_at asc