ERROR Converting data types to SQL Server

Question

ERROR Converting data types to SQL Server

Navigation

#1 by (2 votes)

2

I start by explaining my problem, my database is an inventory and as such I have products and categories, so each product belongs to a different category. I want to make a query where I can count the number of products that each category has and in turn select only those categories that have less than 3 products in it. I have managed to count the number of products by category as follows:

SELECT  Categorias.Nombre_Categoria, count(Productos.Nombre_Producto) as 'Cantidad' FROM Categorias INNER JOIN Productos ON Categorias.ID_Categoria = Productos.ID_Categoria group by Categorias.Nombre_Categoria  order by Categorias.Nombre_Categoria

The problem is when I want to filter the results with the condition that only categories with less than 3 products are shown. I've tried it in the following way by adding WHERE :

SELECT  Categorias.Nombre_Categoria, count(Productos.Nombre_Producto) as 'Cantidad' FROM Categorias INNER JOIN Productos ON Categorias.ID_Categoria = Productos.ID_Categoria where 'Cantidad' < 3 group by Categorias.Nombre_Categoria order by Categorias.Nombre_Categoria

but immediately returns the following error:

Conversion failed when converting the varchar value 'Amount' to data type int.

I want to know how I can convert the values of the column 'Quantity' into integers to filter the results of the query, or how to perform the query that counts the number of products that each category has and select only those with less than 3 products.

I hope you can help me.

sql sql-server

asked by MendezUr 03.09.2016 в 22:44

source

1 answer

woocommerce 2.6.4 delete sidebar in individual products Subtraction problems with decimals in javascript [duplicate]

score 2 · Accepted Answer

Where is used when you need to filter before that Group By performs the grouping.

To filter after that the Group By performs the grouping, use Having , which is the present case:

    SELECT Categorias.ID_Categoria, Categorias.Nombre_Categoria, count(Productos.ID_Producto) as 'Cantidad'
    FROM Categorias INNER JOIN Productos
    ON Categorias.ID_Categoria = Productos.ID_Categoria
    GROUP BY Categorias.ID_Categoria, Categorias.Nombre_Categoria 
        HAVING count(Productos.ID_Producto) < 3    --    <-- Solucion
    ORDER BY Categorias.Nombre_Categoria

I use ID because I think it's faster for the engine to use ID numbers instead of the names text.

An example of a filter equal to the previous one, but before Group By perform the grouping, it is presented when only the products whose names start with the letter A must be taken into account:

    SELECT Categorias.ID_Categoria, Categorias.Nombre_Categoria, count(Productos.ID_Producto) as 'Cantidad'
    FROM Categorias INNER JOIN Productos
    ON Categorias.ID_Categoria = Productos.ID_Categoria
        WHERE Productos.Name LIKE 'A%'
    GROUP BY Categorias.ID_Categoria, Categorias.Nombre_Categoria 
    HAVING count(Productos.ID_Producto) < 3
    ORDER BY Categorias.Nombre_Categoria