Basic SQL Code questions - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Basic SQL Code questions

Author

Topic

SmirnoffKid
Starting Member

10 Posts

Posted - 2005-11-17 : 15:57:51

I have created two tables and inserted the data using insert statements. The two tables that I have created are Automobile and Dealer_Info and have a 1:M relation from Dealer_Info > Automobile.

I am trying to list the Vehicle_Model and the number of times they are repeated, but when I run the code (below) the Count is inserted into an arbitrarily made up attribute (No column name). When that happens I cannot sort in DESC order.

I am using SQL(2000)


Select Vehicle_Model, Count(Vehicle_Model) from Automobile
       Group by (Vehicle_Model)
       Order by count(*)
;

Results:

Vehicle_Model (No column name)
Escort 1
F150 4
Explorer 6
Taurus 12

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-11-17 : 16:02:43

Try this:

Select Vehicle_Model, Count(Vehicle_Model) from Automobile as Frequency
Group by (Vehicle_Model)
Order by Frequency
;

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

SmirnoffKid
Starting Member

10 Posts

Posted - 2005-11-17 : 16:11:41

Fixed it - I just reversed some code


Select Vehicle_Model, Count(Vehicle_Model) as Frequency from Automobile
       Group by (Vehicle_Model)
       Order by Frequency DESC
;

Thankyou

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-11-17 : 16:13:07

Hit me in the head with a hammer!

Select Vehicle_Model, Count(*) as Frequency
from Automobile
Group by (Vehicle_Model)
Order by Frequency
;

like this

DROP TABLE #Astronauts
CREATE TABLE #Astronauts
(Mission nvarchar (04) NULL,
Last_Name nvarchar (20) NULL,
First_Name nvarchar (20) NULL,
Address nvarchar (30) NULL,
City nvarchar (20) NULL,
State nvarchar (2) NULL,
Zip nvarchar (5) NULL
)
GO

INSERT #Astronauts VALUES ('1915','Quetzalcoatl','Quentin ','6615 Pluto Parkway ','Indianapolis','IN','46237')
INSERT #Astronauts VALUES ('2608','Sineca ','Saturnna','11716 Mercury Mile 7th Floor','Kansas City ','MO','64134')
INSERT #Astronauts VALUES ('4179','Toutatis ','Trajan ','77 Apollo Ave. Suite 13 ','Sanford ','FL','32773')
INSERT #Astronauts VALUES ('6318','Conkrite ','Candy ','80 N. W. 58th Street #90 ','Kalispell ','MT','59901')
INSERT #Astronauts VALUES ('1915','Quetzalcoatl','Quentin ','6615 Pluto Parkway ','Indianapolis','IN','46237')

GO

SELECT Last_Name, Count(*) as LNs
FROM #Astronauts
Group by Last_Name
Order by LNs
GO

cut and paste my sample in your SQL Analyzer for an example.

Sorry 'bout that error!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

SmirnoffKid
Starting Member

10 Posts

Posted - 2005-11-17 : 16:20:48

I actually had a couple questions about the same database that I am creating but didn't want to create 4 different threads..

The next problem that I am experiencing wants me to list Vehicle_ID, Model, Color, sorted by Model for all vehicles and have 2/3 options.


Select Vehicle_ID, Vehicle_Model, Vehicle_Color
     from Automobile
     where Air_Conditioning = ('Y')
                              and
     where Four_Wheel_Drive = ('Y')
                              and
     where Air_Conditioning = ('Y')
;

Which is fine if all three have 'Y' as options, but what SQL should I look at (Tiggers?, IF/THEN?, OTHER?] when writing the code to check the three options for 'Y' and if 2/3 of them are 'Y' then list them.

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-11-17 : 16:47:28

Try this:

DROP TABLE #Automobiles
CREATE TABLE #Automobiles
(VIN nvarchar (04) NULL,
Make nvarchar (20) NULL,
Color nvarchar (20) NULL,
_AC nvarchar (1) NULL,
_4WD nvarchar (1) NULL,
_SR nvarchar (1) NULL,
Accessories nvarchar (3) NULL,
_2of3 nvarchar (1) NULL
)
GO

INSERT #Automobiles VALUES ('1915','DeSoto ','Green ','Y','Y','N',' ',' ')
INSERT #Automobiles VALUES ('2608','Packard ','Purple','Y','Y','N',' ',' ')
INSERT #Automobiles VALUES ('4179','Javelin ','Sienna','N','Y','N',' ',' ')
INSERT #Automobiles VALUES ('6318','Schwinn ','Puce ','N','Y','N',' ',' ')
INSERT #Automobiles VALUES ('9990','Ferrari ','Orange','Y','N','N',' ',' ')
GO

UPDATE #Automobiles
SET Accessories = (_AC)+(_4WD)+(_SR)
GO

UPDATE #Automobiles
SET _2of3 =
CASE
WHEN Accessories in ('YYN','YNY','NYY') THEN 'Y'
ELSE 'N'
END
GO

SELECT VIN, Make, Color, _AC, _4WD, _SR, Accessories, _2of3
FROM #Automobiles
WHERE _2of3 = 'Y'
GO

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-11-17 : 17:33:54

HERE's an even better way! If you use numerics instead of Y's and Ns, you can cut down on the amount of code you need!

TRY THIS: (Cut & Paste into SQA)

DROP TABLE #Automobiles
CREATE TABLE #Automobiles
(VIN nvarchar (04) NULL,
Make nvarchar (20) NULL,
Color nvarchar (20) NULL,
_AC int NULL,
_4WD int NULL,
_SR int NULL,
_2of3 int NULL
)
GO

INSERT #Automobiles VALUES ('1915','DeSoto ','Green ',1,1,0,0)
INSERT #Automobiles VALUES ('2608','Packard ','Purple',1,1,0,0)
INSERT #Automobiles VALUES ('4179','Javelin ','Sienna',0,1,0,0)
INSERT #Automobiles VALUES ('6318','Schwinn ','Puce ',0,1,0,0)
INSERT #Automobiles VALUES ('9990','Ferrari ','Orange',1,0,0,0)
GO

SELECT VIN, Make, Color, _AC, _4WD, _SR, SUM(_AC+_4WD+_SR) as _2of3
INTO #Options
FROM #Automobiles
Group by VIN, Make, Color, _AC, _4WD, _SR, _2of3
GO

SELECT VIN, Make, Color, _AC, _4WD, _SR, _2of3
FROM #Options
WHERE _2of3 > 1
GO

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources