CODEWITHSUNDEEP
mysqlsql
Akash
Akash

Reputation: 5012

Combining multiple columns in 1 resultset

I have a table like 

CREATE TABLE sales
    (`id` int, `date` date, `amount` int(4))
;

insert into sales values (1, '2012-09-01', 200),
                         (2, '2012-09-01', 300),
                         (3, '2012-09-02', 400),
                         (4, '2012-09-02', 500),
                         (5, '2012-09-02', 600)

I wish to retrieve a row showing the sales for today, and the sales for yesterday

like

Date             Today Total sales   Yesterday Sales
2012-09-02       1500                500

Tried using something like

SELECT id, date, sum(amount) FROM sales
GROUP BY date;

But it returns the sales day wise. I understand that can be done programmatically, but is there a better way to directly retrieve it from the DB?

sqlfiddle

Upvotes: 0

Views: 151

Answers (3)

Dallin
Dallin

Reputation: 590

SELECT date, sum(amount), yestersales 
FROM sales AS s1, 
    (SELECT sum(amount) as yestersales, ADDDATE(date, 1) AS yesterdate 
        FROM sales GROUP BY date) AS s2
WHERE s1.date = s2.yesterdate
GROUP BY date;

Will do what you want, but it's not really very efficient, I don't think. I would personally do it in code.

Selecting the ID doesn't really make much sense here since you're grouping by date.

Upvotes: 0

sel
sel

Reputation: 4957

SELECT id, date(now()) as `date`, 
      SUM(IF(date(`date`) = date(now()), `amount`, 0)) as TodayTotalSales,
      SUM(IF(date(`date`) < date(now()), `amount`, 0)) as OtherDaySales
FROM sales;

http://sqlfiddle.com/#!2/0ef6a/18

Upvotes: 2

Yogendra Singh
Yogendra Singh

Reputation: 34367

You are getting that because Id is different for each record. You have two option now:

  1. Don't retrieve Id and write query like:

    SELECT date, sum(amount) FROM sales
GROUP BY date;

  2. Use a join with subquery

    SELECT a.ID, a.date, b.amount
FROM sales a, (SELECT date, sum(amount) amount FROM sales
               GROUP BY date) b
WHERE a.date = b.date;

    Please Note: In option 2, second and third columns will be repeating with same value for each id within a day.

Upvotes: 0

Related Questions