SQL Collection

You need some SQL script lads?

Find duplicate user name

SELECT name, COUNT(*) c FROM table GROUP BY name HAVING c > 1;

Get total item from sales order #1

SELECT
 A.delivery_order_id, 
 A.sales_order_id, 
 SUM(B.quantity) AS delivered, 
 C.quantity AS ordered
FROM delivery_order A
 LEFT JOIN delivery_order_item B 
   ON A.delivery_order_id = B.delivery_order_id
 LEFT JOIN sales_order_item C 
   ON B.sales_order_item_id = C.sales_order_item_id
WHERE A.status = 1 AND B.sales_order_item_id = 1

Get product stock of certain product id and certain type from certain owner

SELECT * 
FROM product_stock A
WHERE A.product_id = {$product_id}
	AND A.type = {$type}
	AND A.owner_id = {$owner_id}
	AND A.timestamp =
		(SELECT MAX(B.timestamp) 
		FROM product_stock B 
		WHERE B.product_id = A.product_id 
                  AND B.type = A.type 
                  AND B.owner_id = A.owner_id)

Count total of current stock from specific owner, type, and product

SELECT X.product_id, SUM(X.total)
FROM
(
	SELECT A.product_id, A.type, A.owner_id, SUM(A.stock) as total
	FROM product_stock A
	WHERE A.timestamp = 
			(SELECT MAX(B.timestamp) 
			FROM product_stock B 
			WHERE B.product_id = A.product_id 
			AND B.type=A.type AND B.owner_id = A.owner_id)
	GROUP BY A.product_id, A.type, A.owner_id
) X
GROUP BY X.product_id

Union and Concat in mysql (The key in union is the field must be the same, you can concat some field in some table so that the field is equal)

SELECT *
FROM ( 
	SELECT 
		CONCAT(title, ' - ', note) AS description,
		start_timestamp,
		end_timestamp
	FROM agenda
	WHERE user_id = 2
	UNION ALL 
	SELECT 
		description,
		start_timestamp,
		end_timestamp
	FROM activity
	WHERE user_id = 2
) AS calendar

Get total price and total payment in severe normal database

SELECT
	X.name, X.customer_number, 
	IFNULL(SUM(Y.total_price), 0) AS total_price,
	IFNULL(SUM(Y.total_payment),0) AS total_payment
FROM customer X
JOIN (
	SELECT 
		customer_id,
		(SELECT SUM( B.price )
			FROM  sales_order_item B
			WHERE B.sales_order_id = A.sales_order_id
			GROUP BY B.sales_order_id) AS total_price,
		(SELECT IFNULL(SUM( C.amount ),0) AS total_payment
			FROM  invoice C
			WHERE C.status = 1
			AND C.sales_order_id = A.sales_order_id
			GROUP BY C.sales_order_id) AS total_payment
	FROM sales_order A
)Y ON X.customer_id = Y.customer_id
GROUP BY X.customer_id
ORDER BY X.customer_id

You will learn a lot of common SQL samples from here.

Tinggalkan Balasan

Isikan data di bawah atau klik salah satu ikon untuk log in:

Logo WordPress.com

You are commenting using your WordPress.com account. Logout / Ubah )

Gambar Twitter

You are commenting using your Twitter account. Logout / Ubah )

Foto Facebook

You are commenting using your Facebook account. Logout / Ubah )

Foto Google+

You are commenting using your Google+ account. Logout / Ubah )

Connecting to %s