Count Property-Multiple Values in Same Field Not W

# Thread: Count Property-Multiple Values in Same Field Not W

1. Senior Member
Join Date
Dec 1969
Posts
1,013

## Count Property-Multiple Values in Same Field Not W

I need to count different values from the same field and have them print out four separate counts. I also need to be able to sort these by Site ID. Here is how my data looks:<BR><BR>X SiteID<BR>---------------- ---------------------<BR>3 35012<BR>3 12345<BR>3 35012<BR>1 35012<BR>1 35012<BR>3 35012<BR>2 35012<BR>2 12345<BR><BR>So for example, I need to be able to count all the three’s, two’s, and one’s WHERE ((Tbl.SiteID)= &#039 35012&#039) and display the count results individually <BR> <BR>Example <BR>Report for Site ID “35012”<BR><BR>Value 3 = 3 Records<BR>Value 2 = 1 Record<BR>Value 1 = 2 Records<BR><BR>This is the code we are using that isn’t working :<BR><BR>sql = "SELECT COUNT(DECODE(x,1,1,NULL)) count_of_1, COUNT(DECODE(x,2,1,NULL)) count_of_2, COUNT(DECODE(x,3,1,NULL)) count_of_3 FROM FinLog.AppSta WHERE ((Tbl.SiteID)= &#039 35012&#039)"<BR><BR><BR>PLEASE HELP!!!!<BR>

2. Senior Member
Join Date
Dec 1969
Posts
2,493

## RE: Count Property-Multiple Values in Same Field N

Its kinda wierd but try this<BR><BR>SELECT Count(YourTable.SiteID) AS [Count 1]<BR>FROM YourTable<BR>WHERE (((YourTable.SiteID)=35012)) OR (((YourTable.SiteID)=35012)) OR (((YourTable.SiteID)=35012))<BR>GROUP BY YourTable.X, YourTable.X, YourTable.X<BR>HAVING (((YourTable.X)=1)) OR (((YourTable.X)=2)) OR (((YourTable.X)=3));<BR><BR>you will get a result Like<BR> The first record would be the count of 1, the second record would be the count od 2 and the third record would be the count of 3<BR><BR>Count 1<BR>4 <BR>3 <BR>2 <BR>hope that helps<BR><BR>

3. Steve M Guest

## RE: Count Property-Multiple Values in Same Field N

Try somthing like this. Are you sure about the leading space in &#039 35012&#039?<BR><BR>sql = "SELECT X, Count(*) count_of_x FROM FinLog.AppSta WHERE Tbl.SiteID= &#039 35012&#039 GROUP BY X "<BR>...<BR>Do While No RS.EOF<BR>Response.Write "&ltBR&gtX: " & oRS("X") & " has " & oRS("count_of_x")<BR>RS.MoveNext<BR>Loop<BR><BR><B R><BR>

4. Senior Member
Join Date
Dec 1969
Posts
2,493

## Much easier

I was making it more complicated than it needed to be based on their original query

5. Steve M Guest

## RE: Much easier

I&#039m always looking for ways to let the database do the work. <BR>Sorry for the alias I&#039ll choose another. BTW I spelled &#039Not&#039 wrong...

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•