Can you make a SELECT by returning the quantity and the name of each color?

Asked

Viewed 35 times

0

I’m making these two queries to download the colors and the amount of each color in the table bike:

SELECT DISTINCT color FROM bike ORDER BY color ASC
SELECT COUNT(color) FROM bike GROUP BY color ORDER BY color ASC

As I do so in the second query, counts also come with the name of the associated color, in an array only?

php mysql

  • Which database are you using? Sqlite? Mysql? Postgresql? SQL Server? Oracle? DB2?

  • You can give an example of a table bike, with content, and explain the object you wanted to get into your PHP code?

  • mysql, bike has various information and a 10 columns and the column I want and color, I need to return everything in 1 array, I will put what returns.

  • Because your first consultation doesn’t solve your problem?

  • because I need to use both in a foreach displaying like this, color(Qtd)

1 answer

0


The GROUP BY already ensures that will only have one of each color in the output - just pull the two columns at the same time:

SELECT COUNT(*) AS quantidade,
       color
FROM bike
GROUP BY color
ORDER BY color

  • ah, Once it pulls '-', thanks I’ll test it here.

  • I thought I wouldn’t be able to pull another value along with Qtd and I was looking for some way, but I didn’t notice that it follows the same rule if I called another table field.

  • An important detail is that you can only pull "naked" fields (without being inside a COUNT(), SUM(), MAX(), ... ) if they are listed in GROUP BY (but that’s no problem in that case).

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.