Count rows which contain result of each cell from SQL compact server C#

Question

0.00/5 (No votes)

See more:

I've a SQL Compact server 3.5 database which contains multiple entries, each entry should classified with a certain type (For example: Criminal, Civil, Arbitration ... etc)
I want to populate a list or a table with all the classifications in the database (Criminal, Civil, Arbitration ... etc) (this is the easy part)

And to count all rows in database that should be under the same classification (namely, the row would have the classification name within it's data)

Simple Example:
http://i.imgur.com/6eMi8oW.jpg[^]
And the result should be:
http://i.imgur.com/swKFuof.jpg[^]

What I have tried:

The Working Code <3

C#

try
            {
                sqlcon.Open();
                SqlCeCommand cm = new SqlCeCommand("SELECT  AKtype,  COUNT(AKtype) AS AKcount FROM Aklashehat GROUP BY AKtype ORDER BY AKcount", sqlcon);
                SqlCeDataReader sqldr = cm.ExecuteReader();

                    while (sqldr.Read())
                    {
                        ListViewItem item = new ListViewItem((listView1.Items.Count + 1).ToString());
                        item.SubItems.Add(sqldr["AKtype"].ToString());
                        item.SubItems.Add(sqldr["AKcount"].ToString());
                        listView1.Items.Add(item);
                    }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, ex.Message);
            }

Posted 19-Jul-16 6:51am

Sherif Kamel

Updated 26-Jul-16 4:56am

v3

Add a Solution

Comments

Richard Deeming 19-Jul-16 14:06pm

So some combination of GROUP BY[^] and COUNT[^] then?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

User 11060979 · Accepted Answer · 2016-07-19T23:00:00

Solution 1

This should do it:

SQL

SELECT
  Type,
  COUNT(Type)
FROM Aklashehat 
GROUP BY Type
ORDER BY COUNT(Type)

Some Background you can find -among lot other places- here:
SQL GROUP BY Statement[^]

Edit After feedback from OP Sherif Kamel - Professional Profile[^], SQL CE is Little bit different from e.g. MS SQL 2014

SQL

SELECT
  AKtype,
  COUNT(AKtype)  AS AKcount
FROM Aklashehat 
GROUP BY AKtype
ORDER BY AKcount

Posted 19-Jul-16 23:00pm

User 11060979

Updated 26-Jul-16 5:03am

v3

Comments

Sherif Kamel 26-Jul-16 10:35am

There's an error produced " Expressions in the GROUP BY list cannot contain aggregate function" ? Is this something related to SQL Compact Server or Am I doing something wrong in the listview. *Sorry for the late reply, but I was trying to figure it out on my own

[no name] 26-Jul-16 10:39am

Strange, I really tested this in MS SQL Server 2014 and it runs well there.
Really not simply a typo?

Sherif Kamel 26-Jul-16 10:40am

Well, I tried it with SQL CE cmd and It worked perfect, but I still can't get it to work in my application

[no name] 26-Jul-16 10:42am

Can you post your SQL as comment here? Really the c# code part copy/paste :)

Sherif Kamel 26-Jul-16 10:43am

SqlCeCommand cm = new SqlCeCommand("SELECT AKtype, COUNT(AKtype) FROM Aklashehat GROUP BY AKtype ORDER BY COUNT(AKtype)", sqlcon);

[no name] 26-Jul-16 10:53am

Try this for the Moment:

SqlCeCommand cm = new SqlCeCommand("SELECT AKtype, COUNT(*) FROM Aklashehat GROUP BY AKtype", sqlcon);

Sherif Kamel 26-Jul-16 10:53am

Sir, I found it out , I should specify an alias for count ( COUNT(AKcount) AS COUNT ) and use it in order by ( ORDER BY AKcount ). Thanks sir for directing me to the right path , and please update your answer kindly.

[no name] 26-Jul-16 10:58am

Can you please post your final SQL here to avoid mistakes fro mmy side. Thank you a lot.

Sherif Kamel 26-Jul-16 11:00am

Sure sir , I already updated the code and here's the working sql cmd "SELECT AKtype, COUNT(AKtype) AS AKcount FROM Aklashehat GROUP BY AKtype ORDER BY AKcount"

[no name] 26-Jul-16 11:04am

Great, thank you. I added your final SQL to the solution, see [Edit]