Group By/Order By Two Columns - Get Data Without Losing Rows

Question

I have a table number_color_style with the structure as shown below.

This table contains data as shown below(8 rows)

What I want as I output is All records grouped by number_color_id , number_style_id

I tried below query

SELECT *
FROM `number_color_style`
GROUP BY `number_color_id` , `number_style_id`

It gives me output but some rows are missing

What I want is four set of data without missing a single row(should contain all 8 rows). EG:

• 1st set where number_color_id = 1 , number_style_id = 1
• 2nd set where number_color_id = 1 , number_style_id = 2
• 3rd set where number_color_id = 2 , number_style_id = 1
• 4th set where number_color_id = 2 , number_style_id = 2

How can I do it ?

Create table query

CREATE TABLE IF NOT EXISTS `number_color_style` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `img` varchar(100) NOT NULL,
  `number_color_id` int(11) NOT NULL,
  `number_style_id` int(11) NOT NULL,
  `number_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
);

Some sample data

INSERT INTO `jink`.`number_color_style` (`id`, `img`, `number_color_id`, `number_style_id`, `number_id`) VALUES (NULL, 'img1.png', '1', '1', '10'), (NULL, 'img2.png', '1', '1', '10'), (NULL, 'img3.png', '1', '2', '10'), (NULL, 'img4.png', '2', '1', '10'), (NULL, 'img5.png', '2', '1', '10');

Answer 1

You need an aggregate function when you use 'group by', such as count (*). It might be that you are confusing 'group by' with 'order by'.

One possible query would be:

select number_color_id, number_style_id, count (*)
from number_color_style
group by number_color_id, number_style_id

But I think that you want

select *
from number_color_style
order by number_color_id, number_style_id