Counting the quantity of products

Question

Is there any query that returns the quantity of products example I have 10 cell phones, 5 brand a and 5 brand b. This way and not the total count, if yes how to do? Counting them by id.

Answer 1

Let’s take an example: I have the following product table called tb_products:

create table tb_produtos(
 id int(14) not null auto_increment,
 marca varchar(100),
 tipo varchar(100),
 qtd int not null,
 primary key (id)
 );

We now insert some products to make our example:

insert into tb_produtos (marca, tipo, qtd) values ('A', 'CELULAR', 10);
insert into tb_produtos (marca, tipo, qtd) values ('B', 'CELULAR', 15);
insert into tb_produtos (marca, tipo, qtd) values ('A', 'CELULAR', 15);
insert into tb_produtos (marca, tipo, qtd) values ('B', 'CELULAR', 15);
insert into tb_produtos (marca, tipo, qtd) values ('C', 'CELULAR', 45);
insert into tb_produtos (marca, tipo, qtd) values ('A', 'NOTEBOOK', 45);

By selecting our table with the command below we will have the information as per image:

select * from tb_produtos; 

I want to select the quantity per brand of all products where the type is 'CELLULAR'. We execute the command below and we will have result as picture:

select count(marca) as qtd_por_marca, id, marca, tipo, qtd from tb_produtos where tipo = 'CELULAR'
group by marca;

Now I want to know only the quantity of products per brand where they are of type 'CELLULAR' and are of brand A and brand B. We executed the command below and we will have result as image:

select count(marca) as qtd_por_marca, id, marca, tipo, qtd from tb_produtos where tipo = 'CELULAR' AND marca in ('A', 'B')
group by marca;

With Count by id we will have the command/ result in the images below:

select count(id) as qtd_por_marca, id, marca, tipo, qtd from tb_produtos where tipo = 'CELULAR' AND marca in ('A', 'B')
group by marca;

We can still use the command SUM, to add the amount of products we have in each brand in our query, as per command and image below:

select count(id) as qtd_por_marca, id, marca, tipo, SUM(qtd) from tb_produtos where tipo = 'CELULAR' AND marca in ('A', 'B')
group by marca;

The command Count as the name already suggests, is used to count. Already the SUM to add. Now just adapt to your database.