By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,475 Members | 2,037 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,475 IT Pros & Developers. It's quick & easy.

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

100+
P: 176
Hi all.

This is an SQL question, so I hope this is the right place to ask it.

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

I have no idea on how to create such a query. Going through different SQL commands I haven't found anything appropriate for this kind of task. It does seem to me like a useful query to know how to create.

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.

Thanks for you attention, Michael.
Feb 12 '07 #1
Share this Question
Share on Google+
26 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Try this ...

Expand|Select|Wrap|Line Numbers
  1. SELECT Fname, LnameInitial, Count(LnameInitial) As Amount
  2. FROM TableName
  3. GROUP BY Fname, LnameInitial;
  4.  
Mary
Feb 12 '07 #2

100+
P: 176
Try this ...

Expand|Select|Wrap|Line Numbers
  1. SELECT Fname, LnameInitial, Count(LnameInitial) As Amount
  2. FROM TableName
  3. GROUP BY Fname, LnameInitial;
  4.  
Mary
Thanks a lot, Mary. It works. What I don't understand Is why the count function count exactly the distinct rows.

What about an opposite SQL command that will transform the table in second sketch to a table in a first sketch? Is it at all possible?
Feb 12 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Thanks a lot, Mary. It works. What I don't understand Is why the count function count exactly the distinct rows.
This is where the Group by comes in. Group by Fname and then by LnameInitial makes the values distinct and the Count then counts the number of LnameInitial for each group.

What about an opposite SQL command that will transform the table in second sketch to a table in a first sketch? Is it at all possible?
What exactly are you looking for ?
Feb 12 '07 #4

100+
P: 176
This is where the Group by comes in. Group by Fname and then by LnameInitial makes the values distinct and the Count then counts the number of LnameInitial for each group.



What exactly are you looking for ?
Thanks for the explanation.

I have a table of Distribution items, and sometimes there's an amount next to the item. So I would like to create a query that will "expand" this into Distribution items where one record represents one item.

Also I can handle this in an inverted way, by first creating a table where one record is one item, and the create a query that will count the distinct ones, like with the solution you gave me.

I need the both views for that table.

So know I am able to preform the second option. I would like to be able to preform the first option too.
Feb 12 '07 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
Thanks for the explanation.

I have a table of Distribution items, and sometimes there's an amount next to the item. So I would like to create a query that will "expand" this into Distribution items where one record represents one item.

Also I can handle this in an inverted way, by first creating a table where one record is one item, and the create a query that will count the distinct ones, like with the solution you gave me.

I need the both views for that table.

So know I am able to preform the second option. I would like to be able to preform the first option too.
Micael can you give me the tablename and fieldnames for the distributions table.
Feb 12 '07 #6

NeoPa
Expert Mod 15k+
P: 31,186
I'm watching with interest as I can't think of a way (short of creating a finite table of possible data to OUTER JOIN to - but as a separate preparatory step. I'm not even sure I could find a way to make that work).
Feb 12 '07 #7

100+
P: 176
I'm watching with interest as I can't think of a way (short of creating a finite table of possible data to OUTER JOIN to - but as a separate preparatory step. I'm not even sure I could find a way to make that work).
NeoPa, you're on to something, I already have heard of that way from some other place. Creating a finite table of possible data is fine with me.
It was suggested there to have a table Integers with i from 1 to lets say 100

Expand|Select|Wrap|Line Numbers
  1. SELECT 1 as Amount , Fname
  2. FROM Integers INNER JOIN myTable
  3. WHERE i between 1 and Amount 
However, there must be a mistake here because Fname isn't on Integers.
So it didn't work.

SQL is a pretty cool language, and the solutions to problems using it sometimes come in a sort of neat/weird hierarchical way.
Feb 13 '07 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
I'm watching with interest as I can't think of a way (short of creating a finite table of possible data to OUTER JOIN to - but as a separate preparatory step. I'm not even sure I could find a way to make that work).
Michael

What is the max number the count is likely to reach and is there some reason we can't use code to do this as it is pretty impossible to find a way to do it in pure sql.

Mary
Feb 13 '07 #9

NeoPa
Expert Mod 15k+
P: 31,186
Technically, it is possible to create a very complicated UNION query in code as long as the max number of records is not too enormous. This would not be a good idea unless absolutely necessary. I suggest that using RecordSet VBA, as Mary is about to explain, is a preferable idea.
Feb 13 '07 #10

NeoPa
Expert Mod 15k+
P: 31,186
NeoPa, you're on to something, I already have heard of that way from some other place. Creating a finite table of possible data is fine with me.
It was suggested there to have a table Integers with i from 1 to lets say 100

Expand|Select|Wrap|Line Numbers
  1. SELECT 1 as Amount , Fname
  2. FROM Integers INNER JOIN myTable
  3. WHERE i between 1 and Amount 
However, there must be a mistake here because Fname isn't on Integers.
So it didn't work.

SQL is a pretty cool language, and the solutions to problems using it sometimes come in a sort of neat/weird hierarchical way.
I'm afraid that what you have here looks more like a VBA type loop rather than SQL. I don't think that concept can work in SQL I'm afraid. I'd be interested to learn otherwise but until then see the other recent posts.
Feb 13 '07 #11

100+
P: 176
Michael

What is the max number the count is likely to reach and is there some reason we can't use code to do this as it is pretty impossible to find a way to do it in pure sql.

Mary
Well, I don't know how it is possible, I've attempted to create a loop in VBA in the following way:

Expand|Select|Wrap|Line Numbers
  1. Dim strSQL as String
  2. strSQL = "INSERT INTO myTable (Feild1, Feild2) VALUES ('" & me.txbText1 & "', '" & me.TxbText2 & "'');"
  3. For i%=1 to me.Amount
  4.    DoCmd.RunSQL strSQL
  5. Next i%
But it fails to retrieve a unique amount from each record of the form.
Feb 13 '07 #12

NeoPa
Expert Mod 15k+
P: 31,186
I've been chatting with Mary & I've thought of a way it could be done with SQL after a table gets created or populated in VBA code.
You need a table with records in it up to the number which is the largest number in your table. Each record should have a field with an incremental number in it (Rec1 = 1; Rec2 = 2; etc). I will try to put together the SQL from this point shortly. Mary is looking at the VBA part for you.
The MetaData of the table you need to create is :
Expand|Select|Wrap|Line Numbers
  1. Table Name=tblCount
  2. Count; Numeric
Feb 13 '07 #13

MMcCarthy
Expert Mod 10K+
P: 14,534
Expand|Select|Wrap|Line Numbers
  1. Public Function populateCountTable()
  2. Dim maxCount As Integer
  3. Dim x As Integer
  4.  
  5.   DoCmd.RunSQL "DELETE * FROM tblCount;"
  6.   maxCount = DMax("[Amount]","TableName")
  7.  
  8.   For x = 1 To maxCount
  9.     DoCmd.RunSQL "INSERT INTO tblCount (Count) VALUES (" & x & ");"
  10.   Next x
  11.  
  12. End Function
  13.  
Feb 13 '07 #14

NeoPa
Expert Mod 15k+
P: 31,186
Expand|Select|Wrap|Line Numbers
  1. SELECT T.Fname,T.LnameInitial
  2. FROM TableName AS T, tblCount AS C
  3. WHERE C.Count<=T.Amount
Feb 13 '07 #15

100+
P: 176
Mary, thanks, I didn't use the code you've gave me, as the code NeoPa gave is enough for "expanding" query of that table so each item can have it's own record.

NeoPa, the code works magically!

Thanks a lot to both of you.
:)
Feb 13 '07 #16

NeoPa
Expert Mod 15k+
P: 31,186
Michael, don't mean to argue but using Mary's code is definitely the better way to go. Manually entering 100 records is fine until you find data that goes beyond that.
If you know absolutely that'll never happen then perhaps it's ok (but I'd use the code anyway as it's more complete).

Glad it's working anyway :)
Feb 13 '07 #17

100+
P: 176
Heh, kick me, but I just don't understand why it works. How exactly the criteria of C.Count <= T.Amount fetches a number of rows, if you'll be so kind to explain?
Feb 13 '07 #18

100+
P: 176
Michael, don't mean to argue but using Mary's code is definitely the better way to go. Manually entering 100 records is fine until you find data that goes beyond that.
If you know absolutely that'll never happen then perhaps it's ok (but I'd use the code anyway as it's more complete).

Glad it's working anyway :)
Actually, I tried Mary's code, and it didn't work, as the amount value is different for each record, and it doesn't preform the opperation correctly. Retrieving the max amount value is also unlogical here.

I didn't explain the whole picture, and I appologize for that. There is a temprorary table with items and amounts for each item which is added to a big table. By your method it is added to the bigger table with the item amounts, and then I can use your query to expand the data so each item will have it's own record. Mary's method is adding data already in that way (of each item having its own record) but it doesn't work.
Feb 13 '07 #19

NeoPa
Expert Mod 15k+
P: 31,186
Actually, I tried Mary's code, and it didn't work, as the amount value is different for each record, and it doesn't preform the opperation correctly. Retrieving the max amount value is also unlogical here.
Michael,
No! No! No!
You seem to misunderstand this. Wait until I explain the process then tell us it's not right (You'll need some good explaining though ;)).
This will have to wait until tomorrow though as it's after 01:30 & I want to go to bed :(
Feb 13 '07 #20

NeoPa
Expert Mod 15k+
P: 31,186
Actually, I tried Mary's code, and it didn't work, as the amount value is different for each record, and it doesn't preform the opperation correctly. Retrieving the max amount value is also unlogical here.

I didn't explain the whole picture, and I appologize for that. There is a temprorary table with items and amounts for each item which is added to a big table. By your method it is added to the bigger table with the item amounts, and then I can use your query to expand the data so each item will have it's own record. Mary's method is adding data already in that way (of each item having its own record) but it doesn't work.
I'll get to this in more detail tomorrow, but if it doesn't work, then the specification is wrong. You can't tell us half the story then wonder why it's not exactly right.
Again, I'll check this over again tomorrow.

BTW Mary was creating the code to my specific instruction. It was a necessary part of the whole.
Feb 13 '07 #21

100+
P: 176
Thanks to you both:)
I didn't know it will go as far as creating an overall solution.
Feb 13 '07 #22

100+
P: 176
To make things clearer here's the sum-up of everything in this thread:

- TempTable
(Lname, Fnameinitials, Amount)

where Lname and FnameInitials are an item.

- MainTable with the same MetaData

The data (items with amounts to each item) is being selected on a form that uses TempTable, then by VBA code is added to MainTable, then deleted from TempTable.

There's a requierment to be able to view MainTable data in both 'compressed' and 'expanded' way, the 'expanded' way is having a record for each item (without amounts).

I asked at first of ways of 'expanding' and 'compressing' data of MainTable via query. Later, Mary gave me a suggestion to add the already 'expanded' data(or, beacuse she didn't know that there's a TempTable, maybe what she ment is to compress the data in MainTable somehow) beacuse then it is easier to 'compress' it via query she gave me that doesn't require a finite number of integers.

With the neat query NeoPa gave me, but which does use a finite number of integers, I'm able to add data from TempTable to MainTable with amounts (in the compressed way) and then view it in the expanded way via the query.
Feb 13 '07 #23

MMcCarthy
Expert Mod 10K+
P: 14,534
I asked at first of ways of 'expanding' and 'compressing' data of MainTable via query. Later, Mary gave me a suggestion to add the already 'expanded' data(or, beacuse she didn't know that there's a TempTable, maybe what she ment is to compress the data in MainTable somehow) beacuse then it is easier to 'compress' it via query she gave me that doesn't require a finite number of integers.
Michael

You misunderstood my post. I posted that code at the request of NeoPa as a way of populating the tblCount Table that he uses in his query. It is not a proposed solution to your question. I was simply helping NeoPa out with his proposed solution.

Mary
Feb 13 '07 #24

NeoPa
Expert Mod 15k+
P: 31,186
In the FROM clause there is an OUTER JOIN (Otherwise known as no join). Both tables are listed but separated by a (,) rather than any type of join.
This results in a recordset where every record of table A is crossed with every record of table B. As the records in the count table (tblCount) are sequentially numbered, the WHERE clause ensures that only the correct number of records are produced from this little sub record set.
Expand|Select|Wrap|Line Numbers
  1. TempTable
  2. Lname     Fnameinitials Amount
  3. Smith     Bob           3
  4. Jones     Jim           1
  5.  
  6. tblCount
  7. Count
  8. 1
  9. 2
  10. 3
  11. 4
  12.  
  13. Results before WHERE clause
  14. Lname     Fnameinitials Amount Count Count<=Amount?
  15. Smith     Bob              3      1     Y
  16. Smith     Bob              3      2     Y
  17. Smith     Bob              3      3     Y
  18. Smith     Bob              3      4     N
  19. Jones     Jim              1      1     Y
  20. Jones     Jim              1      2     N
  21. Jones     Jim              1      3     N
  22. Jones     Jim              1      4     N
  23.  
  24. Results after WHERE clause
  25. Lname     Fnameinitials
  26. Smith     Bob
  27. Smith     Bob
  28. Smith     Bob
  29. Jones     Jim
Feb 13 '07 #25

100+
P: 176
Since this thread hadn't been on the first page of the forum, I thought it had been abandoned. Thanks so much Mary and NeoPa for you work. Thanks NeoPa for the very neat query and your explanation of how it works, you've helped me so much!

Cheers, guys and gals.
Feb 19 '07 #26

NeoPa
Expert Mod 15k+
P: 31,186
Strangely enough, it was only after Mary asked me to have a look at it that I thought I had a solution, only to realise it was impossible (logically).
After giving up on the idea, we continued chatting and thrashing through how to explain that, when the final idea came to me via something Mary said.

I'm very pleased to have been involved as it's another interesting way of creating something a little outside-the-box from a SQL construct.
Feb 19 '07 #27

Post your reply

Sign in to post your reply or Sign up for a free account.