making a query using COUNT

Question

I want to have one table with many tables. Before I put the Count(taskid), it works well.

private DataTable GetData() //get any data
{
    string connString = @"Data Source=aa.net;Initial Catalog=db_c;Persist Security Info=True;User ID=db_c_ExternalWriter;Password=aa";
    string query = "Select a.Name, b.Restaurant,  a.City, c.[Postal Code], a.[Open Date], a.POSSystem FROM Stores a, tblConcepts b, tblStates c WHERE a.ConceptID = b.Restaurantid AND  a.State = c.Stateid AND a.[Open Date] >= cast(cast(getdate() as date) as datetime";
   // string query2 = "SELECT Count(Taskid) FROM tblAssignedTasks WHERE Complete = 'True' GROUP BY StoreID";
    using (SqlConnection con = new SqlConnection(connString))
    {
        using (SqlCommand comm = new SqlCommand(query))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                comm.Connection = con;
                sda.SelectCommand = comm;
                using (DataTable dt = new DataTable())
                {
                    sda.Fill(dt);
                    return dt;
                }
            }
        }
    }
}

But when I add Count(taskid) it doesn't work. SELECT Count(Taskid) FROM tblAssignedTasks WHERE Complete = 'True' GROUP BY StoreID; I want to add these queries, any ideas?

private DataTable GetData() //get any data
{
    string connString = @"Data Source=aa.net;Initial Catalog=db_c;Persist Security Info=True;User ID=db_c_ExternalWriter;Password=aa";
    string query = "Select a.Name, b.Restaurant,  a.City, c.[Postal Code], a.[Open Date], a.POSSystem, d.Count(Taskid) FROM Stores a, tblConcepts b, tblStates c, tblAssignedTasks d WHERE a.ConceptID = b.Restaurantid AND  a.State = c.Stateid AND a.[Open Date] >= cast(cast(getdate() as date) as datetime AND b.Restaurantid = d.StoreID AND  d.Complete = 'True' GROUP BY d.StoreID)";
   // string query2 = "SELECT Count(Taskid) FROM tblAssignedTasks WHERE Complete = 'True' GROUP BY StoreID";
    using (SqlConnection con = new SqlConnection(connString))
    {
        using (SqlCommand comm = new SqlCommand(query))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                comm.Connection = con;
                sda.SelectCommand = comm;
                using (DataTable dt = new DataTable())
                {
                    sda.Fill(dt);
                    return dt;
                }
            }
        }
    }
}

When you say it doesn't work, what exactly happens? Throws exception, returns unexpected result, etc? — LB2
– LB2, Commented Jun 6, 2017 at 20:33
@LB2 I can't see the data for the second query. Saying Incorrect syntax near the keyword 'AND'. — Scarlett
– Scarlett, Commented Jun 6, 2017 at 20:35
Have you tested the query directly in SQL? Your problem appears to be a mis placed right parenthesis. — Jacob H
– Jacob H, Commented Jun 6, 2017 at 20:36
@Jacob H Yes the first one works well, and I want to add the Count(taskid) to the table, but I failed. — Scarlett
– Scarlett, Commented Jun 6, 2017 at 20:38
You need to close your first cast with a right parenthesis after datetime, and remove the one after the end of the statement. And add the necessary columns to the group by. Aggregates are all or nothing (all columns need grouped or aggregated, or none of them). — Jacob H
– Jacob H, Commented Jun 6, 2017 at 20:40

Jonathan Applebaum · Accepted Answer · 2017-06-06 21:35:11Z

1

You need to fix your query.
when you are using aggregate function (Count()) you must include all fields inside GROUP BY clause. also,change the wrong syntax of that line d.Count(Taskid) as 'taskKid' (and give an alias name ) to Count(d.Taskid) as 'taskKid'
another fix: a.[Open Date] >= cast(cast(getdate() as date) as datetime is wrong changed it to a.[Open Date] >= getdate()
this should work:

    SELECT a.Name, 
           b.Restaurant,  
           a.City, 
           c.[Postal Code], 
           a.[Open Date],
           a.POSSystem, 
           Count(d.Taskid) as 'taskKid' 
      FROM Stores a,
           tblConcepts b, 
           tblStates c, 
           tblAssignedTasks d 
     WHERE a.ConceptID = b.Restaurantid
       AND a.State = c.Stateid 
       AND a.[Open Date] >= getdate() 
       AND b.Restaurantid = d.StoreID 
       AND d.Complete = 'True' 
  GROUP BY a.Name,
           b.Restaurant,
           a.City, 
           c.[Postal Code], 
           a.[Open Date],
           a.POSSystem

edited Jun 6, 2017 at 21:35

answered Jun 6, 2017 at 20:39

Jonathan Applebaum

6,0164 gold badges40 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Scarlett Over a year ago

Thanks! But it still doesn't work..and I just want to use GROUP BY for the below query. SELECT Count(Taskid) FROM tblAssignedTasks WHERE Complete = 'True'. So I can have Count(Taskid) data when b.Restaurantid = d. StoreID.

Jonathan Applebaum Over a year ago

@JacobH tnx, a.[Open Date] >= cast(cast(getdate() as date) as datetime is wrong changed it to a.[Open Date] >= getdate() . try it now.

Jacob H Over a year ago

There is nothing wrong with that query from what we can tell (you haven't included the DDL data so True may be an integer but we don't know). If you run the query in SQL what is the error that is returned?

Mohan Singh · Accepted Answer · 2017-06-08 11:13:33Z

0

Replace your sql Query with the below query,Hope it works fine.

string query = "Select COUNT(Taskid) OVER() Taskid, a.Name, b.Restaurant,  a.City, c.[Postal Code], a.[Open Date], a.POSSystem, d.Count(Taskid) FROM Stores a, tblConcepts b, tblStates c, tblAssignedTasks d WHERE a.ConceptID = b.Restaurantid AND a.State = c.Stateid AND a.[Open Date] >= cast(cast(getdate() as date) as datetime AND b.Restaurantid = d.StoreID AND  d.Complete = 'True' GROUP BY d.StoreID)";

edited Jun 8, 2017 at 11:13

answered Jun 8, 2017 at 11:04

Mohan Singh

1,1623 gold badges16 silver badges33 bronze badges

Collectives™ on Stack Overflow

making a query using COUNT

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related