Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL MySQL
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 31-Jan-13 0:08am
Edited 31-Jan-13 0:44am
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
0 Dnyaneshwar@Pune 719
1 Sergey Alexandrovich Kryukov 300
2 Peter Leow 280
3 CPallini 275
4 CHill60 235
0 Sergey Alexandrovich Kryukov 9,008
1 OriginalGriff 6,771
2 Peter Leow 4,262
3 Zoltán Zörgő 3,769
4 Richard MacCutchan 2,623


Advertise | Privacy | Mobile
Web04 | 2.8.150128.1 | Last Updated 31 Jan 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100