SQL Counting using multiple columns in the same table

Question

I'm making a database which simulates a store. I've got a table named ProductsTbl that resembles all products available for purchase, and a column named ProductType that identifies the item as a Game or Console. I want to count how many Games fit for each Console. Games have a field called Platform, which is the Console the Game runs on.

SELECT
ProductsTbl.ProductID,
ProductsTbl.ProductName,
ProductsTbl.Manufacturer,
count(ProductsTbl.ProductName = ProductsTbl.Platform)
FROM productstbl
WHERE ProductsTbl.ProductType = 'Console';

This was the only lead I had so far..

Joachim Isaksson · Accepted Answer · 2014-04-27 11:43:59Z

2

You'll need to join the table with itself to get the game list. If (as it looks) the thing that connects the game to the console is that the game's platform is set to the productname of the console, this should do it;

SELECT p1.productid, p1.productname, p1.manufacturer, COUNT(p2.productid) games
FROM productstbl p1
LEFT JOIN productstbl p2 
  ON p2.producttype = 'game' AND p2.platform = p1.productname
WHERE p1.producttype = 'Console'
GROUP BY p1.productid, p1.productname, p1.manufacturer;

answered Apr 27, 2014 at 11:43

Joachim Isaksson

182k28 gold badges298 silver badges308 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Counting using multiple columns in the same table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related