Periodic Table of Elephants - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Periodic Table of Elephants

Author

Topic

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-08-31 : 17:54:13

I have a table wherein each column will contain sundry elements. I have no idea how many different kinds of elements I have. But I need to know how many of each different type. Anyone got an idea? SELECT COUNT won't do it because I have to first know what I'm counting. How can I get, what we call in ADABAS/Natural, a histogram---or what we call a PROC FREQ in SAS????

Thanks....

Here's my test code:

DROP TABLE [Fowler].[dbo].[Animals]

CREATE TABLE [Fowler].[dbo].[Animals]
(Type varchar(20) NULL,
Sightings_2MBC int NULL,
Sightings_2005 int NULL,
Last_Location varchar(10) NULL)
GO
INSERT Animals VALUES('African Elephant ',40,50,'Uganda ')
INSERT Animals VALUES('Jurassic Mammoth ',24,0, 'Delaware ')
INSERT Animals VALUES('Thoroughbred Zebra ',0,0, 'Kentucky ')
INSERT Animals VALUES('Alien from Pollux ',4,78, 'The Moon ')
INSERT Animals VALUES('Supersized Housecat ',1111,2222, 'Delaware ')
INSERT Animals VALUES('Armored Cockroach ',24,0, 'Delaware ')
INSERT Animals VALUES('Greek Pegasus ',1,1, 'Greece ')
INSERT Animals VALUES('Venus Dog-Trap ',0,13, 'Oklahoma ')
INSERT Animals VALUES('Blue Archeopteryx ',24,0, 'Uganda ')
GO

SELECT * FROM [Fowler].[dbo].[Animals] GO

Now what I'm expecting is a list that will tell me there are 3 '24's in Sightings_2MBC, 4 zeros in Sightings_2005 and 3 Delawares, 2 Ugandas, 1 Moon, 1 Kentucky, 1 Greece, and 1 Oklahoma in the last column.

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

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-08-31 : 19:52:51

How about a Crosstab?

Or why not just use COUNT?

SELECT Sightings_2MBC, COUNT(*) FROM Animals GROUP BY Sightings_2MBC
UNION ALL
SELECT Sightings_2005, COUNT(*) FROM Animals GROUP BY Sightings_2005
.
.
.

---------------------------
EmeraldCityDomains.com

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-09-01 : 11:29:12

Thanks, Mark......but I don't know how a Crosstab would work using my test code....in fact in Rob Volk's write up, it looks like you have to declare a cursor to use it.

P.S. You ever been to Caldwell, Ohio?

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

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-09-01 : 12:25:15

>> Now what I'm expecting is a list that will tell me there are 3 '24's in Sightings_2MBC, 4 zeros in Sightings_2005 and 3 Delawares, 2 Ugandas, 1 Moon, 1 Kentucky, 1 Greece, and 1 Oklahoma in the last column.

I have trouble visualizing the resultset, can you draw it for us please ?

Here is my attempt:

SELECT IDENTITY(INT,1,1) AS i, Sightings_2MBC, COUNT(*) AS Cnt_2MBC INTO #a FROM Animals GROUP BY Sightings_2MBC
SELECT IDENTITY(INT,1,1) AS i, Sightings_2005, COUNT(*) AS Cnt_2005C INTO #b FROM Animals GROUP BY Sightings_2005
SELECT IDENTITY(INT,1,1) AS i, Last_Location, COUNT(*) AS Cnt_Location INTO #c FROM Animals GROUP BY Last_Location

SELECT Sightings_2MBC,Cnt_2MBC,Sightings_2005,Cnt_2005C,Last_Location,Cnt_Location
FROM #a FULL JOIN #b ON #a.i = #b.i FULL JOIN #c ON #a.i = #c.i

DROP TABLE #a,#b,#c

Sightings_2MBC Cnt_2MBC    Sightings_2005 Cnt_2005C   Last_Location Cnt_Location 
-------------- ----------- -------------- ----------- ------------- ------------ 
0              2           0              4           Delaware      3
1              1           1              1           Greece        1
4              1           13             1           Kentucky      1
24             3           50             1           Oklahoma      1
40             1           78             1           The Moon      1
1111           1           2222           1           Uganda        2

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-09-01 : 14:03:46

>>or what we call a PROC FREQ in SAS????

<totallyJoking>
Here at the sqlteam we call you a "PROC FREQ"
</totallyJoking>

<partiallyJoking>
when generating your DDL for us, loose the database name. (fowler)
</partiallyJoking>

<deadSerious>
Include your desired output in a form we don't have to guess at
</deadSerious>

<moodDuringPost>

</moodDuringPost>

Be One with the Optimizer
TG

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-09-01 : 14:42:26

Moose & TG....it's really really difficult to read the code you posted when I'm laughing my @

off!!

If I had a few extra beers--they'd be yours!!!

Moose, again, thanks for reading my mind! That's exactly what I was trying to achieve!

TG....the play on the SAS term "Proc Freq" was a side splitter, I'm near tears with laughter on that one!

You guys make SQLTeam ROCK!!! Thanks again!!!

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

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-09-01 : 16:46:54

>> If I had a few extra beers--they'd be yours!!!
All gone eh!
Same here

½!

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-09-02 : 14:26:46

quote:
Originally posted by Xerxes

P.S. You ever been to Caldwell, Ohio?

I purchased that town not long ago from my grandfather. Prior to that, I owned Caldwell, Idaho which is closer to where I grew up.

---------------------------
EmeraldCityDomains.com

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-09-03 : 13:39:23

quote:
Originally posted by AjarnMark

quote:
Originally posted by Xerxes

P.S. You ever been to Caldwell, Ohio?
I purchased that town not long ago from my grandfather. Prior to that, I owned Caldwell, Idaho which is closer to where I grew up.

---------------------------
EmeraldCityDomains.com

Mark could you tell me what an "SQL Slashing Gunting Master" is? How does someone "gunt"? And is that even legal this side of the Mississippi?

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

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-09-03 : 14:07:58

http://www.lotusselfdefense.com/CornerIssues/Issue3.html

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-09-04 : 12:28:51

quote:
Originally posted by robvolk

http://www.lotusselfdefense.com/CornerIssues/Issue3.html

Uh...thanks...

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

SMerrill
Posting Yak Master

206 Posts

Posted - 2005-09-09 : 15:07:39

v. intr.
To utter a deep guttural sound, as a hog does.
To utter a sound similar to a grunt, as in disgust.

LOL

~ Shaun Merrill
Seattle, WA

SamC
White Water Yakist

3467 Posts

Posted - 2005-09-09 : 15:24:50

Gunting would require close proximity.

In an act of self defense, I would toss my cat at the attackers head.

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-09-09 : 17:19:30

Too funny!

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