SQL question about counting the existence of a distinct value in a tab

[inso18]

Users and Experts:

I asked a similiar question in SQL forum but maybe I should ask it here.

I'm intersted in "compressing" a table to distinct values of a field or several fields, and having another field with the amount of existence of each distinct value in the following way:

original table:
Fname.....LnameInitial
----------....-----------------
John.........D.
John.........B.
George.....C.
Sam.........D.
Sam.........D.
Tom..........A.
Tom..........A.
Tom..........A.

"compressed" table or query:
Fname...LnameInitial..Amount
---------...---------------..-----------
John........D. ................1
John........B. ................1
George....C. ................1
Sam........D. ................2
Tom.........A. ............... 3

Does anyone know how to preform it?

Also, if it's not too much to ask, I'm intersted in creating exactly the opposite query that will create the original table from the compressed one.

Thank you.

 

[PHV]

A starting point:
SELECT Fname, LnameInitial, Count(*) AS Amount
FROM [original table]
GROUP BY Fname, LnameInitial

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[inso18]

Thanks, that works for "compressing". Can you explain me how and why Count(*) count the appearance of distinct Fname fields?

Also, can you give me a clue on how to preform the opposite, "decompress" task?

Thanks again.,

 

[traingamer]

Also, can you give me a clue on how to preform the opposite, "decompress" task?

Not to be facetious, but just select the data from the table?

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[inso18]

I was talking about a case of already having a "compressed" table.

 

[PHV]

Rudy gave you a valid answer here: thread220-1333198

 

[traingamer]

I was talking about a case of already having a "compressed" table.

It isn't possible to uncompress data into something that isn't stored in the compressed table. You could simply select from the original table.

What are you actually trying to accomplish?

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[inso18]

Greg, I'm trying to recive the table that is on the first sketch from the table that is on the second sketch.

PHV, that answer doesn't work.

 

[PHV]

Which SQL code doesn't work ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[inso18]

The one you've linked me to.

 

[PHV]

I asked for YOUR actual SQL code not working ...

 

[inso18]

I've used the code suggested to me in the link you gave me.

 

[PHV]

The code you've posted in this thread is NOT the code suggested by Rudy.
So, again, why not posting your actual code ?

 

[inso18]

There isn't code posted in this thread accept of the neat code you gave me on how to count distinct values. I'm now talking about a code of the opposite process, which is what Rudy gave me:

create table integers 
(i integer not null primary key);
insert into integers (i) values
(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

now you can generate as many rows as the Amount value, assuming no Amount value is greater than 9, using the following join

select 1 as Amount
     , EventDate
  from integers
inner
  join yourtable
 where i between 1 and Amount

(which doesn't work) But I've already came to a solution by people who helped me in combining the correct code. If you are interested I can post it.

 

[PHV]

Have you created the integers table with the 10 rows suggested ?
Now, the "decompressing" SQL:
SELECT Fname, LnameInitial
FROM [compressed table], integers
WHERE i Between 1 And Amount

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[inso18]

Yes I did. I did everything Rudy told me to. There is a solution already. If you are intersted I can ask the one who combined it if it's ok to post it.

 

[inso18]

Also, I'm sure Rudy's solution works and he knows what he's talking about. Maybe I didn't correctly explain the whole situation or Rudy and you didn't correctly understood me.