CODEWITHSUNDEEP
mysqlsql
Anik Sarker Akash
Anik Sarker Akash

Reputation: 3

I'm trying to display total no. of orders by each vendor every year?

I'm trying to display total no. of orders by each vendor every year?

Table: Sales

Table: Products

Table: Vendors & Vendor_Info

select year(orderdate) as date_year,count(orderid) as count, OrderID 
from products 
NATURAL JOIN sales 
NATURAL JOIN vendor_info 
WHERE products.Vendor_Id=vendor_info.Vendor_Id  
group by year(orderdate);

on doing this query i'm getting

query result

Where am i going wrong ? and what would the most efficient way to do this?

-- Table structure for table `products`
--

CREATE TABLE IF NOT EXISTS `products` (
  `Product_id` varchar(255) NOT NULL,
  `OrderId` int(11) NOT NULL,
  `Manufacture_Date` date DEFAULT NULL,
  `Raw_Material` varchar(255) DEFAULT NULL,
  `Vendor_Id` int(11) DEFAULT NULL,
  PRIMARY KEY (`Product_id`),
  KEY `OrderId` (`OrderId`)
);

-- Table structure for table `sales`
--

CREATE TABLE IF NOT EXISTS `sales` (
  `OrderID` int(11) NOT NULL AUTO_INCREMENT,
  `OrderDate` date DEFAULT NULL,
  `OrderPrice` int(11) DEFAULT NULL,
  `OrderQuantity` int(11) DEFAULT NULL,
  `CustomerName` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`OrderID`)
) AUTO_INCREMENT=10 ;

-- Table structure for table `Vendors`
--

CREATE TABLE IF NOT EXISTS `Vendors` (
  `Raw_material` varchar(255) DEFAULT NULL,
  `Vendors` varchar(255) DEFAULT NULL,
  `Vendor_id` int(11) DEFAULT NULL,
  KEY `Vendor_id` (`Vendor_id`)
)

-- Table structure for table `Vendor_info`
--

CREATE TABLE IF NOT EXISTS `Vendor_info` (
  `Vendor_id` int(11) DEFAULT NULL,
  `Vendor_name` varchar(255) DEFAULT NULL,
  KEY `Vendor_id` (`Vendor_id`)
)

Upvotes: 0

Views: 653

Answers (2)

nacho
nacho

Reputation: 5397

If you want to get the number or orders by vendor and year yoy can use a query like this one:

select vendor_info.vendor_name,year(sales.orderdate),count(distinct products.orderId)
from vendor_info 
    join products on vendor_info.vendor_id=productos.vendor_id
    join sales on products.orderid=sales.orderid
group by vendor_info.vendor_name,year(sales.orderdate)

Where you make a join from vendors to products and sales. Here you will get only the vendors who have sales.

Upvotes: 0

O. Jones
O. Jones

Reputation: 108686

Some guesswork here, because you have not explained your requirement.

I guess you want to summarize numbers of orders by year. You can do that with a simpler query than your example, because all the data you need is in your sales table.

  select year(orderdate) as date_year, count(orderid) as count
    from sales
   group by year(orderdate);

If this does not meet your need, please edit your question to give us more details.

Upvotes: 1

Related Questions