CODEWITHSUNDEEP
mysql
EleventyOne
EleventyOne

Reputation: 7512

Getting a COUNT() from a GROUP BY

I've got an incredibly convoluted SQL query (three INNER JOINS) that results in an easy to read result set as follows (simplified). I have inherited the db, so it's impossible to change the structure of any of the existing tables, and therefore I have to perform the convoluted query to get to this point:

product_category    |   product_code
------------------------------------
Hardware                102
Hardware                104
Hardware                102
Software                205
Software                104

If I then simply do a GROUP BY product_category, product_code, I get most of the final result set I'm interested in:

product_category    |   product_code
------------------------------------
Hardware                102
Hardware                104
Software                205
Software                104

However, what's missing is number in stock:

product_category    |   product_code    |   num_in_stock
--------------------------------------------------------
Hardware                102                 2
Hardware                104                 1
Software                205                 1
Software                104                 1

Since I want to be able to COUNT() directly from the processing done by the GROUP BY statement, I'm a little lost.

Here is the SQL query thus far:

SELECT categories.product_category, codes.product_code FROM stock
INNER JOIN products ON stock.product_id = products.id
INNER JOIN codes ON products.code_id = codes.id
INNER JOIN categories ON codes.category_id = categories.id
GROUP BY categories.product_category, codes.product_code

The tables are as follows:

So the reason this is so messy is because of the useless version # field in PRODUCTS. What this means is that for a particular combo (e.g., "Hardware 102") it can be entered in PRODUCTS multiple times, each with different version # values, which will then cause STOCK to refer to different ids from PRODUCTS, even though, to me, it's the same product. Ugh!

Any ideas?

Edit:

So let's say there's a product "Misc 999" that has two different versions. This means that there will an entry in CATEGORIES of "Misc", in CODES of "999" (with a category_id of that belonging to "Misc"), and two entries in PRODUCTS (both with the same code_id but with different version info - which I'm ignoring).

Then, if we have 10 of these in stock (3 of one version and 7 of the other, but I'm ignoring version info) there will be 10 entries in the STOCK table, each of which will refer to the PRODUCTS table through an id (two different ids, in this case).

Upvotes: 1

Views: 78

Answers (2)

user359040
user359040

Reputation:

Just add count(*) to your select clause:

SELECT categories.product_category, codes.product_code, count(*) qty_in_stock
FROM stock
INNER JOIN products ON stock.product_id = products.id
INNER JOIN codes ON products.code_id = codes.id
INNER JOIN categories ON codes.category_id = categories.id
GROUP BY categories.product_category, codes.product_code

SQLFiddle here.

Upvotes: 1

BellevueBob
BellevueBob

Reputation: 9618

It's not entirely clear what you want, but perhaps this works:

SELECT categories.product_category
     , codes.product_code
     , SUM(num_in_stock) as num_in_stock
FROM (
   SELECT product_id
        , count(*) as num_in_stock 
   FROM stock
   group by product_id
   ) a
INNER JOIN products 
ON a.product_id = products.id
INNER JOIN codes 
ON products.code_id = codes.id
INNER JOIN categories 
ON codes.category_id = categories.id
GROUP BY categories.product_category
       , codes.product_code

Upvotes: 1

Related Questions