CODEWITHSUNDEEP
mysql
NewBIe
NewBIe

Reputation: 299

Compare data using the year in a query

I have a data and they are recorded by each year, I am trying to compare two years( the past year and the current year) data within one mysql query

Below are my tables

Cost Items

| cid   |   items   |
| 1     |   A       |
| 2     |   B       |

Cost

| cid   | amount    |   year    |
|  1    |   10      |   1       |
|  1    |   20      |   2       |
|  1    |   30      |   1       |

This is the result I am expecting when i want to compare the year 1 and year 2. Year 1 is the past year and year 2 is the current year

Results

items   |   pastCost    |   currentCost |
 A      |     10        |       20      |
 A      |     30        |       0       |

However the below query is what i used by gives a strange answer. 

SELECT 
    IFNULL(ps.`amount`, '0') as  pastCost
    IFNULL(cs.`amount`, '0') as currentCost
FROM
    `Cost Items` b

LEFT JOIN 

    `Cost` ps 
ON 
    b.cID=ps.cID 
        AND 
    ps.Year = 1

LEFT JOIN 
    `Cost` cu 
ON 
    b.cID=cu.cID 
        AND 
    cu.Year =2

This is the result i get from my query

items   |   pastCost    |   currentCost |
 A      |     10        |       20      |
 A      |     30        |       20      |

Please what am i doing wrong? Thanks for helping.

Upvotes: 3

Views: 312

Answers (1)

spencer7593
spencer7593

Reputation: 108430

I'm missing something about your query; the SQL text shown can't produce that result.

There is no source for the items column in the SELECT list, and there is no table aliased as cs. (Looks like the expression in the SELECT list would need to be cu.amount

Aside from that, the results being returned look exactly like what we'd expect. Each row returned from year=2 is being matched with each row returned from year=1. If there were three rows for year=1 and two rows for year=2, we'd get six rows back... each row for year=1 "matched" with each row for year=2.

If (cid, year) tuple was UNIQUE in Cost, then this query would return a result similar to what you expect.

  SELECT b.items
       , IFNULL(ps.amount, '0') AS pastCost
       , IFNULL(cu.amount, '0') AS currentCost
    FROM `Cost Items` b
    LEFT
    JOIN `Cost` ps
      ON ps.cid  = b.cid 
     AND ps.Year = 1
    LEFT
    JOIN `Cost` cu 
      ON cu.cid  = b.cid 
     AND cu.Year = 2

Since (cid, year) is not unique, you need some additional column to "match" a single row for year=1 with a single row for year=2.

Without some other column in the table, we could use an inline view to generate a value. I can illustrate how we can make MySQL return a resultset like the one you show, one way that could be done, but I don't think this is really the solution to whatever problem you are trying to solve:

  SELECT b.items
       , IFNULL(MAX(IF(a.year=1,a.amount,NULL)),0) AS pastCost
       , IFNULL(MAX(IF(a.year=2,a.amount,NULL)),0) AS currentCost
    FROM `Cost Items` b
    LEFT
    JOIN ( SELECT @rn := IF(c.cid=@p_cid AND c.year=@p_year,@rn+1,1) AS `rn` 
                , @p_cid := c.cid AS `cid`
                , @p_year := c.year AS `year`
                , c.amount
             FROM (SELECT @p_cid := NULL, @p_year := NULL, @rn := 0) i
             JOIN `Cost` c
               ON c.year IN (1,2)
            ORDER BY c.cid, c.year, c.amount
         ) a
      ON a.cid = b.cid
   GROUP
      BY b.cid
       , a.rn

A query something like that would return a resultset that looks like the one you are expecting. But again, I strongly suspect that this is not really the resultset you are really looking for.

EDIT

OP leaves comment with vaguely nebulous report of observed behavior: "the above solution doesnt work"

Well then, let's check it out... create a SQL Fiddle with some tables so we can test the query...

SQL Fiddle here http://sqlfiddle.com/#!9/e3d7e/1

create table `Cost Items` (cid int unsigned, items varchar(5));
insert into `Cost Items` (cid, items) values (1,'A'),(2,'B');
create table `Cost` (cid int unsigned, amount int, year int);
insert into `Cost` (cid, amount, year) VALUES (1,10,1),(1,20,2),(1,30,1);

And when we run the query, we get a syntax error. There's closing paren missing in the expressions in the SELECT list, easy enough to fix.

  SELECT b.items
       , IFNULL(MAX(IF(a.year=1,a.amount,NULL)),0) AS pastCost
       , IFNULL(MAX(IF(a.year=2,a.amount,NULL)),0) AS currentCost
    FROM `Cost Items` b
    LEFT
    JOIN ( SELECT @rn := IF(c.cid=@p_cid AND c.year=@p_year,@rn+1,1) AS `rn` 
                , @p_cid := c.cid AS `cid`
                , @p_year := c.year AS `year`
                , c.amount
             FROM (SELECT @p_cid := NULL, @p_year := NULL, @rn := 0) i
             JOIN `Cost` c
               ON c.year IN (1,2)
            ORDER BY c.cid, c.year, c.amount
         ) a
      ON a.cid = b.cid
   GROUP
      BY b.cid
       , a.rn

Returns:

  items   pastCost  currentCost  
  ------  --------  -----------
  A             10           20
  A             30            0
  B              0            0

Upvotes: 2

Related Questions