MySQL Subqueries issue

Question

To make this example simple, lets pretend my table has the following attributes.

Table

ID: Int
Amount: Int
nameId: Int

Records Ex

ID: 1
Amount: 2
nameId: 3

ID: 2
Amount: 2
nameId: 3

ID: 3
Amount: 1
nameId: 3

Currently I have the following query.

SELECT DISTINCT(amount) FROM server.`inventory` where nameid = 558

It gives me

Amount 1
Amount 2

Straight forward, it selects a unique records base on amount. However, how can I also include in my query so that it counts how many id's are using that amount with nameid 558

So the output should be

Amount 1, Used 1
Amount 2, Used 2

What does your question have to do with subqueries?

Barmar
– Barmar

2015-04-03 03:01:38 +00:00
Commented Apr 3, 2015 at 3:01 — Barmar
– Barmar, Commented Apr 3, 2015 at 3:01

Gordon Linoff · Accepted Answer · 2015-04-03 02:58:44Z

1

This is an aggregation query. You want group by and count():

SELECT amount, COUNT(*)
FROM server.inventory 
WHERE nameid = 558
GROUP BY amount;

answered Apr 3, 2015 at 2:58

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

MySQL Subqueries issue

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