Help writing a complex join query

Question

Hello All,

I am having a table orders

orders (
id  int unsigned not null,
fcr_date  TIMESTAMP,
completion_date TIMESTAMP,
factory_no  varchar(255),
vendor_no  varchar(255))

Please ignore the data type typos if any.

I want to write a sql query that helps me fetch the data per vendor factory. The data to fetch includes the number of orders per vendor factory(a unique group of vendor_no, factory_no), vendor_no, factory_no and the number of orders for which fcr_date is greater than completion_date.

I am new to sql and this particular query seems quite complex to me. I would appreciate if some one could guide me thro on how to write this query.

Thanks.

Answer 1

You can try something like this

SELECT  vendor_no,
     factory_no,
     COUNT(1),
     SUM(CASE WHEN fcr_date > completion_date THEN 1 ELSE 0 END)
FROM    @orders
GROUP BY vendor_no,
     factory_no

Answer 2

SELECT   vendor_no,
         factory_no,
         COUNT(id),
         SUM(IF(fcr_date > completion_date, 1, 0))
FROM     orders
GROUP BY vendor_no, factory_no;

Works for me with MySQL.