count records and display with Special field
ID case date time ---------------------------------- 101 A 2001 1 101 A 2002 2 102 A 2001 1 103 B 2001 1 101 B 2003 1 101 A 2004 3 102 C 2006 1 101 B 2007 2 103 C 2007 1 what is sql query for for ID, count every case and show just date for time 1. sample: Output for ID=101 : ID case date qty ------------------------------------- 101 A 2001 3 101 B 2003 2 Please guide me about this.
Best Answer
I found the answer. select id, case, min(time), count(*) as qty from table group by id, case having id=101
I would change your query to select id, case, min(time), count(*) as qty from table where id = 101 group by id, case; The having clause happens after everything has been aggregated in the query, there WHERE clause happens before and will filter your data, although this is small query, you'll see bigger performance gains in the larger queries. [MSDN - Logical Processing Order of the SELECT statement][1] [1]:
https://docs.microsoft.com/en-us/sql/t-sql/queries/select-transact-sql?view=sql-server-2017#logical-processing-order-of-the-select-statement
