Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 203
  • Last Modified:

Select * display distinct

Situation:

I have a database that has appointments being submitted constently. When an appointment is submitted lots of information is sent, but i only need to retreive key elements. One of those is "msr". I tried to select distinct but it only would output a total of 7 appointments for the 7 msr's but there were a total of 15 appointments. So i am thinking that i need to select msr with the date ( so it pulls all the msr names that set appointments that day ). But, instead of just listing all of the 15 msr names ( that were pulled from the select msr ) I just need it to display the distinct records, and a total count of the records.

Example of Database:

AppointmentID - MSR - other info
391 - Joe - Other info
392 - Jill - Other info
393 - Billy - Other info
394 - Billy - Other info
395 - Jill - Other info

Example of disired output:

Joe - 1
Jill - 2
Billy - 2

Not sure how this would go, up until now i have been manipulating the output of a repeater or datagrid or whatever by the select, but in this situation i am completly unsure how to do this .. Any help would rock!!


Thanks!
0
Armon14
Asked:
Armon14
  • 7
  • 5
1 Solution
 
laotzi2000Commented:
try this
select msr, count(*) from yourtable
group by msr
0
 
Armon14Author Commented:
So the string would look like "SELECT msr, Count(*) FROM FED_0001 WHERE SubmitDate LIKE '" & Trim(TodayDate) & "' GROUP BY msr" ?

If not - how would i do it? never seen that group by.
0
 
laotzi2000Commented:
yeah, i think so.
you can try it out in query analyzer.
the output would be like

msr      
Joe   1
Jill    2
Billy  2

and you can give the column a name
select msr, count(*) as an
from fed_0001
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Armon14Author Commented:
Ok cool - and would i use a repeater, or another data control?
0
 
Armon14Author Commented:
Sorry all the questions -- not in a place where i can test / try the query .. trying to get all the info i can so it takes the least amount of guess work :)
0
 
laotzi2000Commented:
you can treat what it returns as a table. that's it.
0
 
Armon14Author Commented:
Cool - i'll try it out when I get home!
0
 
Armon14Author Commented:
Okay - the query works great ! i'm having some issues displaying the record count for the MSR ( i.e. Billy, Jill, and Joe ). I'm using an asp:Repeater to display the msr's, the code is below

      <asp:Repeater id="msr1" runat="server" >
<ItemTemplate>
&nbsp;<%#Container.DataItem("msr")%><br /><br />
</ItemTemplate>
    </asp:Repeater>

What would i place in that to make it show how much it counter per msr?

Thanks!
0
 
laotzi2000Commented:
Like I said, give that column a name,
then use that name like you use msr to get the value of that column
0
 
Armon14Author Commented:
Strange, now it is only displaying the # of appts each MSR got.. it took away their name?
0
 
laotzi2000Commented:
there are two columns here, you can get there respctive value by the name of the column.
for example, if you query is like
select msr, count(*) as mycount from fed2001
group by msr

then you can get the values by "msr" and "mycount"
0
 
Armon14Author Commented:
Ah sweet - sorry i'm so stupid !

Thanks for all the help!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now