MySQL – Show Specific Rows Based on Another Column

group byMySQL

I'm having a hard time figuring out how to do this, and I can't explain it well.
Since I don’t know how to explain it I can’t seem to locate an answer online by simple research. So, I have to draw it out and ask for assistance.
Here are the details:

Columns for the example: My original SQL query created this table (Data specific to this problem comes from one HUGE table)

Sample code:

SELECT 
inventory .01 as  `Item#`, 
inventory .02 as `warehouse`, 
inventory .03 as `qty`, 
inventory .04 as `price`, 
inventory .05 as `weight`, 
inventory .06 as `other`
FROM
Inventory
WHERE
Inventory.other = 5

Returns this:

Item# | warehouse | qty | price | weight | other
1           1        1      3        4       5
1           2        6      3        4       5
1           3        3      3        4       5

I've re-written the table using group by to make the data look like this:

SELECT 
inventory .01 as  `Item#`, 
inventory .03 as `qty (whse1)`, 
inventory .03 as `qty (whse2)`, 
inventory .03 as `qty (whse3)`, 
inventory .04 as `price`, 
inventory .05 as `weight`, 
inventory .06 as `other`
FROM
Inventory
WHERE
Inventory.other = 5
GROUP BY
inventory .01

Returns This:

Item# | qty (whse1)| qty (whse2) | qty (whse3) | price | weight | other
  1         1            1             1           3        4       5

Here’s where I’m stuck. I don’t know how to tell write the SQL code to make the 3 warehouse fields to show like this:

Item# | qty (whse1)| qty (whse2) | qty (whse3) | price | weight | other
  1         1            6             3           3        4       5

I’m sure this is easy to accomplish. I just can’t figure it out.

My DB contains 30K rows, and each item is included 10 times (once per warehouse.) I want to be able to see 3K rows instead, based on the item no, with the qty per warehouse. This is just a sample of the data, the real tables and fields are clearly labeled. This is just a sample of the issue I'm dealing with.

The price, weight and other are all the same for all the items. The only real difference for these parts is the qty per warehouse.

Adding to give real code example. The responses I've received so far seem to come from creating a table from scratch vs. selecting data from an existing table. I've tried implementing the example and I'm getting errors. I can attach a screenshot of some of the data to give you an example of the content included in the database.

SELECT
inventory.`CODE` AS `Item No.`,
inventory.INV_DESCRIPTION AS Description,
COALESCE(inventory.ONHAND,0) AS `On Hand (LA)`,
COALESCE(inventory.ONHAND,0) AS `On Hand (SF)`,
COALESCE(inventory.ONHAND,0) AS `On Hand (HP)`,
COALESCE(inventory.ONHAND,0) AS `On Hand (CHINA)`,
COALESCE(inventory.ONHAND,0) AS `On Hand (INDO)`,
pricing.BVRTLPRICE01 AS `SF Warehouse`,
pricing.BVRTLPRICE02 AS `LA Warehouse`,
pricing.BVRTLPRICE03 AS `HP Warehouse`,
pricing.BVRTLPRICE04 AS `FOB China/Indonesia`,
pricing.BVRTLPRICE05 AS ECOMM,
inventory.CUBE,
inventory.WEIGHT,
inventory.DIMENSION,
inventory.PROD AS `Status`
FROM
inventory
LEFT JOIN inventory.ONHAND on (inventory.`Item No.` = inventory.`On Hand (LA)` 
AND inventory.WHSE=00)
LEFT JOIN inventory.ONHAND on (inventory.`Item No.` = inventory.`On Hand (SF)` 
AND inventory.WHSE=10)
LEFT JOIN inventory.ONHAND on (inventory.`Item No.` = inventory.`On Hand (HP)` 
AND inventory.WHSE=20)
LEFT JOIN inventory.ONHAND on (inventory.`Item No.` = inventory.`On Hand (CHINA)
` AND inventory.WHSE=50)
LEFT JOIN inventory.ONHAND on (inventory.`Item No.` = inventory.`On Hand (INDO)
` AND inventory.WHSE=70)
INNER JOIN pricing ON inventory.`CODE` = pricing.BVSPECPRICEPARTNO
WHERE
inventory.WHSE IN (00, 10, 20, 50, 70) AND
inventory.PROD IN ('A', 'A-70', 'B', 'C', 'S', 'KIT', 'SET')
GROUP BY
inventory.`CODE`,
inventory.WHSE ASC
ORDER BY
`Item No.` ASC,
inventory.WHSE ASC,
inventory.PROD

Best Answer

Given this test data: (this is a hint on how to make a more readable question in the future):

create table inventory(
  id int unsigned primary key auto_increment,
  item int unsigned,
  warehouse int unsigned,
  qty int unsigned,
  price int unsigned,
  weight int unsigned,
  other int unsigned);

insert into inventory(item, warehouse, qty, price, weight, other) values
  (1,1,1,3,4,5),
  (1,2,6,3,4,5),
  (1,3,3,3,4,5);

> select item,warehouse,qty,price,weight,other from inventory;
+------+-----------+------+-------+--------+-------+
| item | warehouse | qty  | price | weight | other |
+------+-----------+------+-------+--------+-------+
|    1 |         1 |    1 |     3 |      4 |     5 |
|    1 |         2 |    6 |     3 |      4 |     5 |
|    1 |         3 |    3 |     3 |      4 |     5 |
+------+-----------+------+-------+--------+-------+

You can have the desired output with the following query:

> select distinct
  i.item,
  COALESCE(w1.qty,0) as w1,
  COALESCE(w2.qty,0) as w2,
  COALESCE(w3.qty,0) as w3,
  i.price,
  i.weight,
  i.other
from inventory i
  left join inventory w1 on (i.item = w1.item AND w1.warehouse=1)
  left join inventory w2 on (i.item = w2.item and w2.warehouse=2)
  left join inventory w3 on (i.item = w3.item and w3.warehouse=3);

+------+------+------+------+-------+--------+-------+
| item | w1   | w2   | w3   | price | weight | other |
+------+------+------+------+-------+--------+-------+
|    1 |    1 |    6 |    3 |     3 |      4 |     5 |
+------+------+------+------+-------+--------+-------+

COALESCE function and LEFT JOINs are for the case that there is no row for item, warehouse combination

Related Solutions

Mysql – Slow complex query with group/order

I can see couple things that should improve your query performance.

1 As you already found out there is absolutely no need to join mentioncache. Using EXISTS seems more natural (or IN as you did, but EXISTS may work better from performance point of view).

2 DATE(m.indexed) BETWEEN "2012-09-16" AND "2012-10-16" can be rewritten to m.indexed between "2012-09-16" AND "2012-10-16 23:59:59", so mysql can use index.

3 urlinfluranks doesn't seem to be used anywhere except in LEFT JOIN, why do you need it?

4 f.foreign_id can be either null or m.id, and this is the only reference to favoureditems table, I'd rather use subquery in this case.

Finally, I think you can get the same results without GROUP BY m.id (as far as I understood , mentions.id a primary key).

SELECT   
m.id, m.title, m.title_text, m.content_text, m.url,m.root_url,m.sub_type,m.indexed,  
CASE 
 WHEN EXISTS 
    (SELECT NULL FROM favoureditems f WHERE f.model = "Mention" 
    AND f.foreign_id = m.id AND f.owner_id = 803) THEN m.id 
END AS f.foreign_id,
, v.foreign_id, v.created, mfs.score,  
Image.id,Image.model,Image.foreign_key, Image.dirname,Image.basename,  
(REPLACE(REPLACE(m.host_url, 'http://www.', ''), 'http://', '')) AS Mention__plain_url  
FROM mentions AS m  

LEFT JOIN 
(
  SELECT id,model,foreign_key,dirname,basename 
  FROM attachments Image  
  WHERE model = 'Mention'
  GROUP BY foreign_key
 )Image  ON (Image.foreign_key = m.id)      

LEFT JOIN 
(
   SELECT v.foreign_id, v.created 
   FROM visiteditems AS v  
   WHERE (v.model = "Mention"  AND v.owner_id = 803)  
    GROUP BY v.foreign_id
)v ON (v.foreign_id = m.id)
LEFT JOIN 
(
   SELECT mention_id,score
   FROM mentionfeedscores mfs  
   WHERE mfs.feed_id = '474737584865424564398208323289092'
   GROUP BY mention_id
)mfs ON (mfs.mention_id = m.id )

WHERE m.indexed BETWEEN "2012-09-16" AND "2012-10-16 23:59:59"  
   AND EXISTS 
  (
     SELECT NULL FROM mentioncache mc  
      WHERE mc.mention_id = m.id AND mc.profile_id = 803  
   )    
ORDER BY m.indexed DESC  
LIMIT 10

Sql-server – SQL join query to show rows with non-existent rows in one table

Thank you for SQLfiddle and sample data! I wish more questions started this way.

If you want all members regardless of whether they have an entry for that date, you want a LEFT OUTER JOIN. You were very close with this version however a little trick with outer joins is that if you add a filter to the outer table in the WHERE clause, you turn an outer join to an inner join, because it will exclude any rows that are NULL on that side (because it doesn't know if NULL would match the filter or not).

I modified the first query to get a row for every member:

SELECT Members.Member_ID
      ,Time_Entry.Date_Start
      ,Time_Entry.Hours_Actual
      ,Time_Entry.Hours_Bill
FROM dbo.Members
  LEFT OUTER JOIN dbo.Time_Entry
--^^^^ changed from FULL to LEFT
  ON Members.Member_ID = Time_Entry.Member_ID
  AND Time_Entry.Date_Start = '20131110';
--^^^ changed from WHERE to AND

I'll leave it as an exercise for the reader to take it from there and add the other columns, formatting, COALESCE etc.

Some other notes:

please always use the schema prefix when creating and referencing objects
please always use a length when converting to varchar etc.
stay away from ambiguous, regional date formats like mm-dd-yyyy

consider using aliases to make your queries easier to read. E.g. the above could be re-written as:

SELECT m.Member_ID
  ,t.Date_Start
  ,t.Hours_Actual
  ,t.Hours_Bill
FROM dbo.Members AS m
LEFT OUTER JOIN dbo.Time_Entry AS t
ON m.Member_ID = t.Member_ID
AND t.Date_Start = '20131110';

... a lot tidier, IMHO, as long as you use sensible aliases.

Best Answer

Related Solutions

Mysql – Slow complex query with group/order

Sql-server – SQL join query to show rows with non-existent rows in one table

Related Question