What I'm trying to do: Get top 10 most used color ints inside the column.

I'm connecting to a SQL Server database and I need to get some values from a table column

1. I have a column inside a data table, lets call it "Cars"
2. Inside the table I have a column called "Date" and "Color"
3. The "Color" column has different values ranging from 0 to 20 Each number inside the color column stands for a color code.

First: I'm checking how many new rows appeared as of today with:

var connection = new SqlConnection(connString);

using (connection)
{
    connection.Open();

    // Total Queries
    using (var cmd = new SqlCommand("SELECT COUNT(*) FROM [db].[Cars] WHERE Date LIKE '"   today   "%'", connection))  
    {
        var aa = Convert.ToInt32(cmd.ExecuteScalar());

        Console.WriteLine("Total car orders today: "    aa);
    }
}

Now what I'm trying to do is get the TOP 10 most used numbers inside the color column. But what I'm getting is either the lowest number or the highest number.

My code below:

using (var cmmd = new SqlCommand(
                "SELECT Color, COUNT (Color) AS ValueFrequency FROM [dbo].[Cars] WHERE QueriedAt LIKE '"   today   "%' GROUP BY Color ORDER BY ValueFrequency ASC", connection))  
{
    var bb = Convert.ToInt32(cmmd.ExecuteScalar());
    Console.WriteLine("Top 10 colors today : "    bb);
}

What I'm getting is either the highest or lowest color int inside the column and not the list of top 10 most appeared colors in my console. I would like to have top 10 most colors printed out in a list.

CodePudding user response：

If you want the top 5 colors of today, shouldn't you simply order by frequency desc?

SELECT TOP 5 Color, COUNT (Color) AS ValueFrequency 
FROM [dbo].[Cars] 
WHERE [Date] >= CAST( GETDATE() AS Date ) AND [Date] < CAST( DATEADD(day, 1, GETDATE()) AS Date )
GROUP BY Color 
ORDER BY ValueFrequency DESC

CodePudding user response：

you need to use group by color and sort descending

CREATE TABLE tableName 
(
    Date    date,
    Color   int,
    Cars    int
);

INSERT INTO tableName (Date ,Color,Cars) VALUES ('2022-09-01 ', '1', '1');
INSERT INTO tableName (Date ,Color,Cars) VALUES ('2022-09-01 ', '3', '2');
INSERT INTO tableName (Date ,Color,Cars) VALUES ('2022-09-01 ', '1', '1');
INSERT INTO tableName (Date ,Color,Cars) VALUES ('2022-09-01 ', '15', '1');
INSERT INTO tableName (Date ,Color,Cars) VALUES ('2022-09-01 ', '20', '3');
INSERT INTO tableName (Date ,Color,Cars) VALUES ('2022-09-01 ', '1', '1');


select top 10 date,color,count(color) as ccnt  from tablename group by date,color order by ccnt desc

CodePudding user response：

Try SELECT TOP 5 Color, COUNT(Color) AS ValueFrequency from table rest where clause/conditions

this will return the top 5 rows