?
Solved

Select * display distinct

Posted on 2005-03-23
12
Medium Priority
?
199 Views
Last Modified: 2012-05-05
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
Comment
Question by:Armon14
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
12 Comments
 
LVL 12

Expert Comment

by:laotzi2000
ID: 13615172
try this
select msr, count(*) from yourtable
group by msr
0
 

Author Comment

by:Armon14
ID: 13615206
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
 
LVL 12

Expert Comment

by:laotzi2000
ID: 13615290
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
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.

 

Author Comment

by:Armon14
ID: 13615309
Ok cool - and would i use a repeater, or another data control?
0
 

Author Comment

by:Armon14
ID: 13615319
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
 
LVL 12

Expert Comment

by:laotzi2000
ID: 13615327
you can treat what it returns as a table. that's it.
0
 

Author Comment

by:Armon14
ID: 13615333
Cool - i'll try it out when I get home!
0
 

Author Comment

by:Armon14
ID: 13615748
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
 
LVL 12

Expert Comment

by:laotzi2000
ID: 13615775
Like I said, give that column a name,
then use that name like you use msr to get the value of that column
0
 

Author Comment

by:Armon14
ID: 13615803
Strange, now it is only displaying the # of appts each MSR got.. it took away their name?
0
 
LVL 12

Accepted Solution

by:
laotzi2000 earned 2000 total points
ID: 13615848
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
 

Author Comment

by:Armon14
ID: 13615870
Ah sweet - sorry i'm so stupid !

Thanks for all the help!
0

Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lots of people ask this question on how to extend the “MembershipProvider” to make use of custom authentication like using existing database or make use of some other way of authentication. Many blogs show you how to extend the membership provider c…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question