CODEWITHSUNDEEP
mysqlselect
Adrian P.
Adrian P.

Reputation: 5228

Tips to make one single query from these 3 MySQL SELECT queries?

I have this MySql table and its data:

CREATE TABLE IF NOT EXISTS `invoices` (
  `invoice_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `invoice_owner` int(11) NOT NULL,
  `customer_id` int(11) NOT NULL,
  `invoice_no` int(11) NOT NULL,
  `invoice_date` date NOT NULL,
  `invoice_due_date` date NOT NULL,
  `invoice_status` enum('open','cancelled','overdue','closed','archived') NOT NULL,
  `tax1_desc` varchar(50) NOT NULL,
  `tax1_rate` float(6,3) NOT NULL,
  `tax2_desc` varchar(50) NOT NULL,
  `tax2_rate` float(6,3) NOT NULL,
  `invoice_total` float(11,2) NOT NULL DEFAULT '0.00',
  `invoice_notes` text,
  PRIMARY KEY (`invoice_id`),
  KEY `customer_invoice` (`customer_id`,`invoice_no`),
  KEY `invoice_owner` (`invoice_owner`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1005 ;



INSERT INTO `invoices` (`invoice_id`, `invoice_owner`, `customer_id`, `invoice_no`, `invoice_date`, `invoice_due_date`, `invoice_status`, `tax1_desc`, `tax1_rate`, `tax2_desc`, `tax2_rate`, `invoice_total`, `invoice_notes`) VALUES
 (999, 1, 0, 999, '2012-12-13', '2013-01-13', 'archived', '', 0.000, '', 0.000, 255.48, NULL),
(1000, 1, 0, 1000, '2013-04-14', '2013-05-14', 'cancelled', '', 0.000, '', 0.000, 105.28, NULL),
(1001, 1, 0, 1001, '2013-04-13', '2013-05-13', 'closed', '', 0.000, '', 0.000, 202.33, NULL),
(1002, 1, 0, 1002, '2013-04-15', '2013-05-14', 'open', '', 0.000, '', 0.000, 1113.85, NULL),
(1003, 1, 0, 1003, '2013-03-25', '2013-04-25', 'overdue', '', 0.000, '', 0.000, 114.75, NULL),
(1004, 0, 0, 1004, '2013-02-28', '2013-03-28', 'overdue', '', 0.000, '', 0.000, 2890.56, NULL);

I have to make a select that will give me 3 sums:

  1. total overdue invoices

    SELECT SUM (invoice_total) AS overdue FROM invoices where invoice_status = 'overdue'

  2. get total overdue for 1-30 days

    SELECT SUM (invoice_total) AS overdue FROM invoices where invoice_status = 'overdue' AND invoice_due_date BETWEEN invoice_due_date+1 AND invoice_due_date+30

  3. get total overdue for over 30 days

    SELECT SUM (invoice_total) AS overdue FROM invoices where invoice_status = 'overdue' AND invoice_due_date >= invoice_due_date+31

Of course, total overdue = overdue 1-30 days + overdue over 30 days

QUESTION: How can I do all that in one SQL query?

I have to return 3 numbers: Total overdue, overdue 1-30, overdue over 30

Upvotes: 1

Views: 229

Answers (1)

Joachim Isaksson
Joachim Isaksson

Reputation: 180917

Using some MySQLisms to make it a bit shorter, something like this should do it; just using 3 separate sums over the same rows;

SELECT 
  SUM(invoice_total) AS overdue1, 
  SUM(invoice_total *
    (NOW() BETWEEN invoice_due_date + INTERVAL 1 DAY 
     AND invoice_due_date + INTERVAL 30 DAY)) AS overdue2,
  SUM(invoice_total *
    (NOW() > invoice_due_date + INTERVAL 30 DAY)) AS overdue3
FROM invoices
WHERE invoice_status = 'overdue'

An SQLfiddle to test with.

Upvotes: 1

Related Questions