x

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.

more ▼

asked Apr 13 at 08:00 PM in Default

avatar image

hossein61
1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

I found the answer.

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

answered Apr 13 at 09:40 PM

avatar image

hossein61
1

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Apr 17 at 09:53 AM

avatar image

WRBI
61 1 3

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x53

asked: Apr 13 at 08:00 PM

Seen: 21 times

Last Updated: Apr 17 at 09:53 AM

Copyright 2018 Redgate Software. Privacy Policy