Home » SQL & PL/SQL » SQL & PL/SQL » Help with Select statement..
Help with Select statement.. [message #233815] Fri, 27 April 2007 11:57 Go to next message
RocaWear2007
Messages: 11
Registered: April 2007
Junior Member
Hi, I was wondering if somebody could help me out here. Basically my table at the moment looks something like this:

Name - Items - Number of Items
User 1 Item1, Item 1, Item1 3
User 2 Item 2, Item 1 2
User 1 Item 4, Item 1, Item 3 3
User 3
User 3
User 2
User 1

The name is in the first column, the second column holds the items(after each item there is a comma and then a space) in the third column there is a number(that number shows the amount of items there are in the second column).

I need help with a SELECT statement which will add up all the users, show all items they have, and sum up the number of items, so basically something looking like the following:

Name - Items - Number of Items
User 1 Item1, Item 1, Item1, Item 4, Item 1, Item 3 6
User 2 Item 2, Item 1 2
User 3

Any help or suggestions please?
Re: Help with Select statement.. [message #233820 is a reply to message #233815] Fri, 27 April 2007 12:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Search for stragg here and on AskTom.

Regards
Michel
Re: Help with Select statement.. [message #233821 is a reply to message #233820] Fri, 27 April 2007 12:19 Go to previous messageGo to next message
RocaWear2007
Messages: 11
Registered: April 2007
Junior Member
Sorry, I cant use Stragg as im doing this in Vb.Net
Re: Help with Select statement.. [message #233823 is a reply to message #233821] Fri, 27 April 2007 12:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at the followings:
http://www.dba-village.com/dba/village/dvp_forum.OpenThread?ThreadIdA=24558#79562
http://www.dba-village.com/dba/village/dvp_forum.OpenThread?ThreadIdA=15156#60829

Regards
Michel
Re: Help with Select statement.. [message #233863 is a reply to message #233823] Fri, 27 April 2007 15:47 Go to previous messageGo to next message
RocaWear2007
Messages: 11
Registered: April 2007
Junior Member
Thanks for that, it kind of helped on parts of it.

If under a columns I had text and numbers, and I wanted to sum all of the numbers, how would I go about doing that? Would I use the LIKE function?
Re: Help with Select statement.. [message #233894 is a reply to message #233863] Sat, 28 April 2007 00:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a test case: create table and insert statements and I'll be glad to find and show a solution.

Regards
Michel
Re: Help with Select statement.. [message #233992 is a reply to message #233815] Sat, 28 April 2007 14:27 Go to previous messageGo to next message
RocaWear2007
Messages: 11
Registered: April 2007
Junior Member
Hey thanks and Ive found out the solutions I needed to use SUMS, however I was wondering if you could help me on the following please.

Basically I have a database created, and the layout of it is shown below:

Date/Room - Lesson1 - Lesson2 - Lesson3 - Lesson4 - Lesson5
Date1/Room1
Date1/Room2
Date1/Room3
Date1/Room4
Date1/Room5
Date2/Room1
Date2/Room2
Date2/Room3
Date2/Room4
Date2/Room5
Date3/Room1
Date3/Room2
Date3/Room3
Date3/Room4
Date3/Room5
And so on and so on.

It’s a timetable where people book rooms, the way it works is I have a date and then a room. The dates are the same for 5 records, as each record is for another room. Then when the user books a room, they choose a room and all the records are shown for that room e.g if they choose room1 the records are shown like

Date1/Room1
Date2/Room1
Date3/Room1

The user then enters their name and the details get saved into the database, all of this is working perfectly. However im working on the statistics part of the program, what I need help on is I need to list all names, and the amount of times they have booked a room. Could you give me any advice on how this would be achievable please? Thanks

Thing is im not using oracle, im doing this in Vb.net, so just a Select statement would be fine.
Re: Help with Select statement.. [message #233993 is a reply to message #233815] Sat, 28 April 2007 14:33 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
> Thing is im not using oracle, im doing this in Vb.net,
Then please stop bothering us in this Oracle forum & post your problem in a VB forum.
Re: Help with Select statement.. [message #233994 is a reply to message #233815] Sat, 28 April 2007 14:43 Go to previous messageGo to next message
RocaWear2007
Messages: 11
Registered: April 2007
Junior Member
Its the same select statement
Re: Help with Select statement.. [message #233996 is a reply to message #233815] Sat, 28 April 2007 15:00 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>Its the same select statement
Not necessarily true.
If so, the please go post in MS SQLServer, MYSQL, Postgres, DB2, Informix, etc. forum(s) or what ever RDBMS you really are using.
Re: Help with Select statement.. [message #233997 is a reply to message #233992] Sat, 28 April 2007 15:22 Go to previous message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Still don't see a test case.

Regards
Michel
Previous Topic: join query between two same table structure
Next Topic: Merge 2 tables
Goto Forum:
  


Current Time: Wed Dec 07 12:18:29 CST 2016

Total time taken to generate the page: 0.13753 seconds