I need help in "select count(distinct Colunm_Name) from table"

Hi all...

I want to extract the number of distinct rows in "Country" colunm in "Guest_Book" table... It didn't work i need help ....



*******************************************



Dim cmd As New OleDb.OleDbCommand("SELECT COUNT(*) FROM GUEST_BOOK", conn)


da.SelectCommand = cmd


da.Fill(ds)


conn.Open()


Dim totalRows As Integer = cmd.ExecuteScalar()


cmd.CommandText = "SELECT distinct count(distinct Country) FROM GUEST_BOOK"


da.SelectCommand = cmd


da.Fill(ds)


Dim Diff_Countries As Integer = cmd.ExecuteScalar()


conn.close()


Label2.Text = "There are " &amp; totalRows &amp; "</font></b> posts from " &amp; Diff_Countries &amp; " diffrent countries were posted in the guest book "


*******************************************


The first ExecuteScalar is working properly but the second one provide an error :
Syntax error (missing operator) in query expression 'count(distinct Country)'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: Syntax error (missing operator) in query expression 'count(distinct Country)'.

Source Error:

Code:

Line 217:        cmd.CommandText = "SELECT distinct count(distinct Country) FROM GUEST_BOOK"
Line 218:        da.SelectCommand = cmd
Line 219:        da.Fill(ds)
Line 220:
Line 221:        Dim Diff_Countries As Integer = cmd.ExecuteScalar()

Source File: c:\inetpub\wwwroot\ashrafcsnet1\viewGuestBook.aspx.vb Line: 219

Stack Trace:

Code:

[OleDbException (0x80040e14): Syntax error (missing operator) in query expression 'count(distinct Country)'.]
   System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr) +42
   System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) +290
   System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) +156
   System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) +62
   System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) +110
   System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) +110
   System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +5
   System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +153
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +144
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +90
   ashrafcsnet1.viewGuestBook.CountRows() in c:\inetpub\wwwroot\ashrafcsnet1\viewGuestBook.aspx.vb:219
   ashrafcsnet1.viewGuestBook.Page_Load(Object sender, EventArgs e) in c:\inetpub\wwwroot\ashrafcsnet1\viewGuestBook.aspx.vb:128
   System.Web.UI.Control.OnLoad(EventArgs e) +102
   System.Web.UI.Control.LoadRecursive() +45
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +952

Version Information:Microsoft .NET Framework Version:2.0.40607.42; ASP.NET Version:2.0.40607.42 <!--
[OleDbException]: Syntax error (missing operator) in query expression 'count(distinct Country)'.
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at ashrafcsnet1.viewGuestBook.CountRows() in c:\inetpub\wwwroot\ashrafcsnet1\viewGuestBook.aspx.vb:line 219
at ashrafcsnet1.viewGuestBook.Page_Load(Object sender, EventArgs e) in c:\inetpub\wwwroot\ashrafcsnet1\viewGuestBook.aspx.vb:line 128
at System.Web.UI.Control.OnLoad(EventArgs e)
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
[HttpUnhandledException]: Exception of type 'System.Web.HttpUnhandledException' was thrown.
at System.Web.UI.Page.HandleError(Exception e)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
at System.Web.UI.Page.ProcessRequest(HttpContext context)
at System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)
-->

 

I think you need to make that query a group by in order to use any aggregate functions, aka count, sum, avg, etc..


SELECT Count(Country) AS CountDC, Country
FROM GUEST_BOOK GROUP BY Country

This will give you a count of each country.


Otherwise:


SELECT Count(Country) AS CountDC FROM GUEST_BOOK GROUP BY Country

 

Thanx Bruce &amp; Alex for ur reply but even it's still error ...
I donn't know why !! The name of the column is correct too

Syntax error (missing operator) in query expression 'count(distinct Country)'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: Syntax error (missing operator) in query expression 'count(distinct Country)'.

Source Error:

Code:

Line 222:        cmd.CommandText = "SELECT count(distinct Country) FROM GUEST_BOOK"
Line 223:        da.SelectCommand = cmd
Line 224:        da.Fill(ds)
Line 225:
Line 226:        Dim Diff_Countries As Integer = cmd.ExecuteScalar()

Source File: c:\inetpub\wwwroot\ashrafcsnet1\viewGuestBook.aspx.vb Line: 224

OR


Syntax error (missing operator) in query expression 'count(distinct Country)'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: Syntax error (missing operator) in query expression 'count(distinct Country)'.

Source Error:

Code:

Line 222:        cmd.CommandText = "SELECT count(distinct Country) FROM GUEST_BOOK GROUP BY Country"
Line 223:        da.SelectCommand = cmd
Line 224:        da.Fill(ds)
Line 225:
Line 226:        Dim Diff_Countries As Integer = cmd.ExecuteScalar()

Source File: c:\inetpub\wwwroot\ashrafcsnet1\viewGuestBook.aspx.vb Line: 224


without distinct it works well and returned the number of countries but with repetition

Any help would be appreciated...
Thanx again...

 

This is a workaround : create a query in your database that selects 'distinct' allcountries : i.e. "select distinct country as dCountriesfrom GUEST_BOOK", (name : GuestbookCountries)


Then use this to get a count of the distinct countries. : "select count(dCountries) from GuestbookCountries"





--
Steurm
www.steurm.net/steurm