Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old September 19th, 2005, 10:10 PM
Registered User
 
Join Date: Sep 2005
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default dumb question

ok a dumb one here but i need some help.

I have records as such in access db

Name ID StatusCode

Name is a list of names
ID is an ID funnily nuff
StatusCode is a string that can be any of 50 different values.

So i have records like this

Name ID StatusCode
Dan 1 05
Mary 2 25
Dan 1 25
Dan 1 25


I need a query to do the following:

For each name how many 05's are there, how many 25's etc.
so the result should be something like:

Dan 05 Result : 1
Dan 25 Result : 2
Mary 05 Result : 1


Basically i need the count of rows for each person that match the statuscode.

Any ideas?


 
Old September 20th, 2005, 05:20 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

So, you need, for each set consisting of a grouping of StatusCode by Name, the count of the number of elements in each set?

How about:
Code:
    SELECT Name, ID, COUNT(*) as Result
    FROM yourtable
    GROUP BY Name, StatusCode
Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
 
Old September 29th, 2005, 09:10 AM
Registered User
 
Join Date: Sep 2004
Location: Hyderabad, Sindh, Pakistan.
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to gemsuis Send a message via Yahoo to gemsuis
Default

Mr. Jeff Mason is sujjested you good but you should know that the "Result" will the your count of rows and "yourtable" will be the name of your table






Similar Threads
Thread Thread Starter Forum Replies Last Post
Dumb date to record question topshed Classic ASP Databases 2 June 3rd, 2005 01:42 PM
Dumb Question topshed CSS Cascading Style Sheets 1 April 7th, 2005 04:29 PM
dumb version question on ASP 1.0/1.1/2.0 badgolfer ASP.NET 1.0 and 1.1 Basics 1 October 5th, 2004 07:10 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.