Go Back   TechArena Community > Software > Windows Software
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



SQL Server Count and group

Windows Software


Reply
 
Thread Tools Search this Thread
  #1  
Old 16-04-2011
Member
 
Join Date: Jan 2011
Posts: 101
SQL Server Count and group
  

I have up till now one more project to due that?s due in some days and out of 15 queries there are 2 I am confused on. I can think of them sensibly in my head but I am not capable to actually interpret it into a SQL server query. The two questions are:
  1. List the details for the branches which have as a minimum 1 DVD copy in a minimum of 3 dissimilar groups
  2. List details for the members which have the most recently borrowed copies. Series the output alphabetically by member name
The database is a DVD hire company and there are 5 dissimilar movie category / genres across 7 dissimilar stores. There are quite a lot of copies of each DVD obtainable and there are a bit like 37 DVD titles though lots of copies all over the stores. I am hesitant of how to go regarding the query. As for question 2, I think it's asking, who was the final person to rent a title, in spite of of which copy number it is. Once more, this would be a few sort of min or max as the database has a Date borrowed DATETIME variable in one of the tables, but I am hesitant of how to go about writing the cipher. Any help would be very much appreciated.

Reply With Quote
  #2  
Old 16-04-2011
Member
 
Join Date: Jun 2009
Posts: 1,518
Re: SQL Server Count and group

Well I am in school for "IT" although I have not at all been bear to any sort of programming prior to Java that I was first introduced to just a year before, and I disliked it. Our textbook is next to ineffective with very basic things. I came here for a few help and support, and if that's how you feel then so be it. I don't actually wish to start any advice so I will just leave this be. As well, just 2 years of "IT" Education and my program consists of more commerce than IT at the moment.
Reply With Quote
  #3  
Old 16-04-2011
Member
 
Join Date: Nov 2008
Posts: 1,514
Re: SQL Server Count and group

I could be mistaken but, I think 2nd question is asking for the member(s) who are presently renting the majority DVDs. e.g. if alice has borrowed 2, bob has borrowed 3, and chris has borrowed 1, you necessitate to output bob's details. If bob and chris have both borrowed 3, you want to output bob's details and chris's details. For question 1, I am guessing you will need to group the dvdCopy table by branch number and after that do a count on the separate groups(you talk about table DVD is immaterial, but I don?t notice genre or group information in the tables you illustrate, so this information is probable in the DVD table)
Reply With Quote
  #4  
Old 16-04-2011
Member
 
Join Date: Mar 2009
Posts: 1,360
Re: SQL Server Count and group

Here I also wish to add something regarding this query. I have previously done an intro to java course, I passed but not by a great extent. I have no goals to further my knowledge in java. I understand it's possibly the most utilized but I don't preparation to do the programming or coding thing in the future. Not my type of thing. I will continually cracking away at 6 to see if I can make a few additional small dents in the query.
Reply With Quote
  #5  
Old 16-04-2011
Member
 
Join Date: Nov 2008
Posts: 1,259
Re: SQL Server Count and group

For your 1st question:
Code:
SELECT * FROM Branch WHERE (BranchNo) IN (SELECT BranchNo FROM DVDCopy having (MemberNumber is not null and Count (*)>0 group by category, MemberNumber));
How are the groups determined? I may have misinterpreted but I can't make out a field for this. Is it just that there is one DVD for every category, and quite a lot of copies of each DVD? The script above would work if there is a field for group, if not I will seem at it further. Unluckily I don't get paid >50k to write SQL, more like 20k, but I will assist if I can.
Reply With Quote
  #6  
Old 27-04-2011
Member
 
Join Date: May 2008
Posts: 913
Re: SQL Server Count and group

For your second question List details for the members which have the most presently borrowed copies. Series the output alphabetically by member name
Code:
select * from Memb where Membno in (select MembNo from DVDCopy group by MembNo having count (MembNum) = (select max (cnt) from (select MembNum, count (MembNum) cnt from dvdcopy
group by Membnum))) order by name asc;
Once more, if I have read the query properly. There is possibly a more stylish way of doing this, but mainly I have completed a count of all the DVDs grouped by Membnum, and after that linked this over to the member table. Thinking regarding it though you will possibly require to discount too so:
Code:
select * from Memb where Membno in (select MembNo from DVDCopy group by MembNo having count(MembNum) = (select max(cnt) from (select MembNum, count(MembNum) cnt from DVDCopy where MembNum is not null group by membnum))) order by name asc;
Let me know how mistaken these are.
Reply With Quote
Reply

  TechArena Community > Software > Windows Software
Tags: , , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "SQL Server Count and group"
Thread Thread Starter Forum Replies Last Post
How to create new group on PGP Universal Server 3.1.2 McKenzie Networking & Security 6 06-06-2011 05:51 AM
How Active directory group access SQL server FlayoFish Operating Systems 3 11-08-2009 09:18 AM
Server 2003 Group Policy deetech79 Operating Systems 2 03-07-2009 08:48 AM
Server 2008: Moving a Group to Another Domain stephenx Operating Systems 4 15-04-2009 09:01 AM
How to do sysprep to make a image for my users and maintain the count on KMS server ? Motor Wu Vista Help 2 16-03-2007 07:37 AM


All times are GMT +5.5. The time now is 07:29 AM.