count records and display with Special field

Question

 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.

Answer 1

0

I found the answer.

     select id, case, min(time), count(*) as qty
     from table
     group by id, case
     having id=101

answered Apr 13 at 09:40 PM

hossein61
1

(comments are locked)

Answer 2

0

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

answered Apr 17 at 09:53 AM

WRBI
61 ● 1 ● 3

(comments are locked)

	Follow @Ask_SSC
	Follow Ask SSC on Facebook
	Find Ask SSC on Google+
	Find us on LinkedIn

count records and display with Special field

Follow this question

Related Questions