Click here to Skip to main content
12,697,076 members (29,861 online)
Rate this:
 
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
Updated 31-Jan-13 0:44am
v2
Comments
Zoltán Zörgő 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 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
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web01 | 2.8.170118.1 | Last Updated 31 Jan 2013
Copyright © CodeProject, 1999-2017
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