Retrieve data from two different years

TABLE:

CREATE TABLE IF NOT EXISTS `inventory_data` (
inventory_id int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
store_id smallint unsigned NOT NULL,
inventory_year smallint unsigned NOT NULL,
shortage_dollars decimal(10,2) unsigned NOT NULL
)
engine=INNODB;

CREATE TABLE IF NOT EXISTS `stores` (
store_id smallint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
district_id smallint unsigned not null
)
engine=INNODB;

OUTPUT :

I want to be able to retrieve the shortage dollar amounts for two given 
years for all the stores within a given district. Inventory data for 
each store is only added to the inventory_data table when the inventory 
is completed, so not all stores within a district will all be 
represented all the time.  

SQL:

SELECT `s`.`store_id`, `i`.`shortage_dollars` AS `yr2011`, `i1`.`shortage_dollars` AS `yr2012`
FROM `stores` `s`
LEFT JOIN `inventory_data` `i` ON `s`.`store_id` = `i`.`store_id`
    AND `i`.`inventory_year` = 2011
LEFT JOIN `inventory_data` `i1` ON `s`.`store_id` = `i1`.`store_id`
    AND `i1`.`inventory_year` = 2012
WHERE `s`.`district_id` = 1 

Alternative SQL :

SELECT `s`.`store_id`, `i`.`inventory_year`, `i`.`shortage_dollars`
FROM `stores` `s`
LEFT JOIN `inventory_data` `i` ON `s`.`store_id` = `i`.`store_id`
WHERE `s`.`district_id` = 1
AND `i`.`inventory_year` IN (2011, 2012)
ORDER BY `s`.`store_id`, `i`.`inventory_year`

the product with the highest price

I have a vehicle (vin, vmaker, vmodel, vyear) customer (SSN, cname, cgender, ccity) buyvehicle (BVSSN, BVVin, price, year) I need a query that finds the vehicle with the highest sale price for each maker. Please help! example: Honda Civic $20000 Honda Accord $25000 BMW 3 Series $22000 BMW 5 Series $40000 the result should be: Honda Accord BMW 5 Series SQL: select v.vmaker, max(b.price) price from vehicle v left join buyvehicle b on v.vin = b.bvvin group by v.vmaker

Get SUM of sales for multiple years in columns

Output :

I would like to get something like this: *Customer *2009 *2010 * |------------|---------|--------| |Peter |120 |240 | |Johe |455 |550 |

Sql : Select c.name, Sum(Case When o.year == 2008 Then price Else 0 End) cy2008, Sum(Case When o.year == 2009 Then price Else 0 End) cy2009, Sum(Case When o.year == 2010 Then price Else 0 End) cy2010 From Customers c left outer join Orders o on o.customer_id = c.customer_id Group By c.name

MySQL query for total sales per month and payment type

Table :
uid | invoice_num | pay_type | trans_date | total

Output :
Trans_date | Cash | Debit | Credit | Total
2013-10-01    1000      0          500     1500

Sql :

SELECT trans_date, 
    CASE WHEN pay_type = 0 THEN sum(total) ELSE 0 END AS Cash, 
    CASE WHEN pay_type = 1 THEN sum(total) ELSE 0 END AS Debit, 
    CASE WHEN pay_type = 2 THEN sum(total) ELSE 0 END AS Credit, 
    sum(total) AS Total
FROM tb_detail_transjual
GROUP BY trans_date

 

Total sales per month

 SELECT YEAR(date) as SalesYear,
         MONTH(date) as SalesMonth,
         SUM(Price) AS TotalSales
    FROM Sales
GROUP BY YEAR(date), MONTH(date)
ORDER BY YEAR(date), MONTH(date)