CODEWITHSUNDEEP
mysqljoinviewsum
codeDragon
codeDragon

Reputation: 565

How to create a view including the following tables?

I have the following 2 tables involved, I am trying to create a view popularProducts, where the sum of each product's quantityOrdered are equal or above 100.

I tried the following but it does't quite looks right to me: 

SELECT productCode, productName, buyPrice, image FROM products JOIN orderDetails on products.productCode=orderDetails.products_productCode WHERE (SELECT SUM(quantityOrdered) >= 100);

OrderDetails table:

CREATE TABLE `orderDetails` (
  `products_productCode` bigint(20) UNSIGNED NOT NULL,
  `orders_orderNumber` bigint(20) UNSIGNED NOT NULL,
  `quantityOrdered` varchar(45) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

And products table: 

CREATE TABLE `products` (
  `productCode` bigint(20) UNSIGNED NOT NULL,
  `productName` varchar(45) DEFAULT NULL,
  `productDescription` text,
  `quantityInStock` smallint(5) UNSIGNED DEFAULT NULL,
  `buyPrice` decimal(7,2) UNSIGNED DEFAULT NULL,
  `image` varchar(45) DEFAULT NULL
) E

Upvotes: 1

Views: 42

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

You need group by and having clause for the sum of quantity ordered

CREATE VIEW viewName AS
SELECT p.productCode, p.productName, p.buyPrice, p.image 
FROM products p
JOIN orderDetails o on p.productCode=o.products_productCode 
GROUP BY p.productCode, p.productName, p.buyPrice, p.image 
HAVING SUM(o.quantityOrdered) >= 100

Upvotes: 1

Related Questions