Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQLMySQL
Hey guys,
 
I am not the strongest SQL developer, I just have a quick question checking if this is possible.
 
I have the following table:
 
Itemsets
itemsetid productid
| 1 | 5 |
| 1 | 6 |
| 2 | 5 |
| 2 | 9 |
 
What I want to do is return all the itemsets and know how many are there.
 
What I want returned:
[[1(5,6)],[2(5,9)]] -> length of this = 2(number of itemsets)
 
Something along these lines.
 
Any help is greatly appreciated,
 
Thanks
Posted 30-Jan-13 23:08pm
Edited 30-Jan-13 23:44pm
v2
Comments
Zoltán Zörgő at 31-Jan-13 5:11am
   
What would this bring for you? I suppose you use SQL server. I suggest you do this on client side, or you can make .net integrated user function for that. Can be done in t-sql also, but I think it is not really good approach.
Member 9736263 at 31-Jan-13 5:36am
   
I'm using java/mysql. I agree with you, I believe I'll have to do this in java. So I can return all the items and make an algorithm to put them into a correct array. Thanks a million for the help
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

select itemsetid, count(1) from  itemsets group by itemsetid
this will give you
 
1 2
2 2
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

This is a special requirement, I suggest you make code for that.
You have 3 possibilities (http://www.codeguru.com/cpp/data/mfc_database/misc/article.php/c12615/MySQL-UDFs.htm[^]):
- Stored procedure[^]
- UDF
- Native function
 
It depends on your programming knowledge and the access you have to the server. If you need fast processing and you can write it in C and deploy the UDF to the server, do it. If not, write a stored procedure.
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Your Filters
Interested
Ignored
     
0 Guruprasad.K.Basavaraju 450
1 Sergey Alexandrovich Kryukov 325
2 Shai Vashdi 318
3 OriginalGriff 265
4 Peter Leow 171
0 Sergey Alexandrovich Kryukov 9,109
1 OriginalGriff 5,290
2 Peter Leow 4,010
3 Maciej Los 3,535
4 Abhinav S 3,263


Advertise | Privacy | Mobile
Web03 | 2.8.140415.2 | Last Updated 31 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Use
Layout: fixed | fluid