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:

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,

Posted 31-Jan-13 0:08am
Updated 31-Jan-13 0:44am
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
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
Rate this: bad
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 ([^]):
- Stored procedure[^]
- 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.

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