I.T. Discussion Community!
-Collapse +Expand
DBA
Search DBA Group:

Advanced
-Collapse +Expand DBA Store
PRESTWOODSTORE

Prestwood eMagazine

March Edition
Subscribe now! It's Free!
Enter your email:

   ► MB LobbyDBA, Databases, & Data BoardMS SQL Topic   Print This     

SQL Query

SQL Query in MS SQL topic (part of our DBA, Databases, & Data group).

Quick Search: SQL Query  
KMerker
Cincinnati
Suppose I have a table that looks like

ID CAR1 CAR2 CAR3 ...
--------------------------------------
001 HONDA PONTIAC FORD
002 NISSAN TOYOTA HONDA
003 MITSU MAZDA NISSAN

etc...

I want to build a query that retreives the total number of occurances of each type of car. For example,

CAR COUNT
--------------
HONDA 2
PONTIAC 1
NISSAN 2
MITZU 1
FORD 1
TOYOTA 1
MAZDA 1

I know how to do it if there is only one column of CAR, however, I'm not sure when you have mulitple columns.
 Posted 19 years ago (Thread Starter)
Comment Quote
About KMerker -Collapse +Expand
Visit Profile
Membership pending.
Member subscribes to this thread but email is NOT VERIFIED.
Email Not Verified!
Once email is verified, we will review and approve the account.
Web Presence Hidden.
Once above is taken care of, full Profile content will display including back links, about me, my message, custom Profile html, social networking links, message board signature, company profile, etc.

Post ID #7720, 2 replies
Thread Started 9/30/2002 3:56:00 AM
View Counter=2281
Last Reply Posted 10/4/2002 9:52:00 AM)
Location=Cincinnati  
Joined=20 years ago   MB Posts=7  
Most Recent Post
jdschram
MN
Yahoo normalization!!!
 Posted 19 years ago
Comment Quote
About jdschram -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #7781 (Level 1.1)  Reply to 7720
Thread Started 10/4/2002 9:52:00 AM
View Counter=2
Location=MN  
Joined=20 years ago   MB Posts=259  
Scott Wehrly
Prestwood IT
 (Inactive)
Las Vegas, NV USA
The simple answer is that this can't be done in a query, because there isn't any way to aggregate the field values when the record structure is de-normalized like this. You could produce a query of several SELECT statements UNIONed together, but it would have to be "hard coded" to the field value of the manufacturer name to work, and if you added a different manufacturer to the data (i.e. Packard), it wouldn't be picked up until you added that name to the combination of SELECTs in the UNION. Very messy.

The "one column of CAR" structure will work, and I wonder why your table structure doesn't fit that design. If you really need to keep a unique key on each row, then expand the fields to add the secondary key, so that the data looks like this:

0001  001  HONDA
0002 001 PONTIAC
0003 001 FORD
0004 002 NISSAN
0005 002 TOYOTA
0006 002 HONDA
0007 003 MITSU
0008 003 MAZDA
0009 003 NISSAN
Without this record structure, you could do this through a stored procedure that calculates the aggregates and returns a dataset of results (or creates a separate result table that can be queried). But this would just be a hard solution to a bad data design.
 Posted 19 years ago
Comment Quote
About Scott Wehrly -Collapse +Expand
Visit Profile
Inactive member.
Member does not subscribe to this thread.
Old Account!
If this is your account, sign in to activate web presence data (sign in quarterly to keep active). Alternatively, you can subscribe to our monthly eMag with a valid email address.
Web Presence Hidden.
Once above is taken care of, full Profile content will display including back links, about me, my message, custom Profile html, social networking links, message board signature, company profile, etc.

Post ID #7758 (Level 1.2)  Reply to 7720
Reply Posted 10/2/2002 3:24:00 PM
Location=Las Vegas, NV USA 
Joined=19 years ago   MB Posts=442   KB Posts=19  

Revive Thread!

Add a comment to revive this old thread and make this archived thread more useful.

Write a Comment...
Full Editor
...
Sign in...

If you are a member, Sign In. Or, you can Create a Free account now.


Anonymous Post (text-only, no HTML):

Enter your name and security key.

Your Name:
Security key = P123A1
Enter key:
Icon: A Post    Thread    Idea    Important!    Cool    Sad    No    Yes    Includes a Link...   
Thread #7720 Counter
2281
Since 4/2/2008

Follow PrestwoodBoards on: 


©1995-2021 PrestwoodBoards  [Security & Privacy]
Professional IT Services: Coding | Websites | Computer Tech