Overclock.net › Forums › Software, Programming and Coding › Coding and Programming › SQL Counting Question
New Posts  All Forums:Forum Nav:

SQL Counting Question

post #1 of 10
Thread Starter 
Hey guys,

I am trying to determine how many faculty members can teach more than two courses from the below table. The answer should be 2 (4756 and 8759) however I am getting 1 and 1. Can anyone tell me what is wrong with my code? Thanks.
Code:
select count(distinct Faculty_ID)
from IS_Qualified_t
group by Faculty_ID having (COUNT(Course_ID)>2) 

-----------
1
1
Code:
Faculty_ID                              Course_ID  Date_Qualified
--------------------------------------- ---------- -----------------------
2130                                    ISM 2000   2000-09-01 00:00:00
2130                                    ISM 3100   1998-09-01 00:00:00
2143                                    ISM 3112   1988-09-01 00:00:00
2143                                    ISM 3113   1988-09-01 00:00:00
3487                                    ISM 4212   1995-09-01 00:00:00
3487                                    ISM 4930   1996-09-01 00:00:00
4756                                    ISM 3112   1991-09-01 00:00:00
4756                                    ISM 3113   1991-09-01 00:00:00
4756                                    ISM 4930   1999-09-01 00:00:00
5233                                    ISM 3112   2004-09-01 00:00:00
5233                                    ISM 3150   2000-09-01 00:00:00
7854                                    ISM 2000   1987-09-01 00:00:00
7896                                    ISM 8745   2003-09-01 00:00:00
8745                                    ISM 6100   2002-09-01 00:00:00
8759                                    ISM 4212   1997-09-01 00:00:00
8759                                    ISM 6500   1999-09-01 00:00:00
8759                                    ISM 1111   1999-09-02 00:00:00
Destructor
(28 items)
 
  
CPUMotherboardGraphicsRAM
Intel i7-3770K @ 4.6GHz  Gigabyte G1.Sniper 3  EVGA GTX 980 Ti SC+  16GB Corsair Dominator Platinum 2400MHz  
Hard DriveHard DriveHard DriveOptical Drive
Samsung 850 Pro 256GB RAID 0 Samsung F3 1TB Seagate 3TB LG Blu-ray 12x 
CoolingOSMonitorKeyboard
XSPC RayStorm D5 RX360  Windows 10 Pro x64 LG 24GM77 Ducky Shine 3 Year of the Snake 
PowerCaseMouseMouse Pad
Corsair HX850 Corsair 900D  Logitech G400  Razer Goliathus Speed  
AudioAudio
Sound Blaster Zx  Sennheiser HD 558 + Zm-Mic1 
  hide details  
Reply
Destructor
(28 items)
 
  
CPUMotherboardGraphicsRAM
Intel i7-3770K @ 4.6GHz  Gigabyte G1.Sniper 3  EVGA GTX 980 Ti SC+  16GB Corsair Dominator Platinum 2400MHz  
Hard DriveHard DriveHard DriveOptical Drive
Samsung 850 Pro 256GB RAID 0 Samsung F3 1TB Seagate 3TB LG Blu-ray 12x 
CoolingOSMonitorKeyboard
XSPC RayStorm D5 RX360  Windows 10 Pro x64 LG 24GM77 Ducky Shine 3 Year of the Snake 
PowerCaseMouseMouse Pad
Corsair HX850 Corsair 900D  Logitech G400  Razer Goliathus Speed  
AudioAudio
Sound Blaster Zx  Sennheiser HD 558 + Zm-Mic1 
  hide details  
Reply
post #2 of 10
well i've no clue about programming in SQL - but to me the code looks like it's looking for greater than 2, of which there is one case (course 3112)

so technically it is working, just I think you need to change the code to look for greater than 1, to get two or more courses.

edit oops, I reread, and you're looking for more than two. Time for me to shut up. smile.gif
Edited by latelesley - 3/27/15 at 8:49am
post #3 of 10
Not experienced in actual SQL, just basic Access but here's my attempt:

What are you trying to get as your output; the number of faculty members (ie 2) or the faculty IDs (ie 4756 and 8759)?

Code:
select Faculty_ID
from IS_Qualified_t
group by Faculty_ID having (COUNT(Course_ID)>2) 

Outputs:
Code:
4756
8759

I think the COUNT(DISTINCT Faculty_ID) is converting all the faculty ID to 1 as it's first creating a list of unique Faculty IDs then returning the count
post #4 of 10
Try this...haven't tested it yet, but will test later today.
Code:
select count(Faculty_ID)
from IS_Qualified_t
group by Faculty_ID having (COUNT(Course_ID)>2)

Edited by surfbumb - 3/27/15 at 10:45am
Black Silence
(15 items)
 
  
CPUMotherboardRAMHard Drive
i5 3570k @ 4.5 GHz Asus P8Z77-M Pro Kingston HyperX Genesis 8 GB - 1600 MHz Seagate Barracuda 250 GB 
Optical DriveCoolingOSMonitor
Samsung WriteMaster Noctua NH-D14 Windows 10 ASUS VS24AH-P 
KeyboardPowerCaseMouse
Logitech Navigator Enermax Infiniti 650W Fractal R3 Black Pearl Razer Death Adder 
Mouse PadAudio
SteelSeries QcK Mass Altec Lansing FX4021 
  hide details  
Reply
Black Silence
(15 items)
 
  
CPUMotherboardRAMHard Drive
i5 3570k @ 4.5 GHz Asus P8Z77-M Pro Kingston HyperX Genesis 8 GB - 1600 MHz Seagate Barracuda 250 GB 
Optical DriveCoolingOSMonitor
Samsung WriteMaster Noctua NH-D14 Windows 10 ASUS VS24AH-P 
KeyboardPowerCaseMouse
Logitech Navigator Enermax Infiniti 650W Fractal R3 Black Pearl Razer Death Adder 
Mouse PadAudio
SteelSeries QcK Mass Altec Lansing FX4021 
  hide details  
Reply
post #5 of 10
Quote:
Originally Posted by surfbumb View Post

Try this...haven't tested it yet, but will test later today.
Code:
select count(Faculty_ID)
from IS_Qualified_t
group by Faculty_ID having (COUNT(Course_ID)>2)

Tested it out, It outputs
Code:
3
3

Its basically only showing the counts of course ID that are >2. I'm not very familiar with SQL code, is it possible to add a additional count line to the end? If you can count the results of GROUP BY, you will get 2.
post #6 of 10
Probably bad performance but should work i think:
Code:
Select count(*)
from IS_Qualified_t a
where a.Faculty_ID =
(select Faculty_ID from IS_Qualified_t b
group by b.Faculty_ID having (COUNT(Course_ID)>2) )

But usually there is a way to do union instead of a subquery with the same result but much better performance.

Edit: doesn't work tongue.gif, i'm so bad at SQL

this works, and I think a subquery is fine in this case, it's not going to do a query for each element of the table, those are the cases where union usually gives much better performance:
Code:
Select count(*)
from (select Faculty_ID from IS_Qualified_t b
group by b.Faculty_ID having (COUNT(Course_ID)>2)) as morethantwolessons

And btw, the distinct is not necessary in your query, group by will group the same items together so they'll already be distinct.
Edited by Sisaroth - 3/27/15 at 11:18am
2013
(11 items)
 
  
CPUMotherboardGraphicsRAM
Intel Core i7 2700k GIGABYTE GA-Z77-D3H Sapphire HD 7870 XT Boost G.SKILL 1866 Sniper 2x4 GB 
Hard DriveHard DriveOSMonitor
Samsung 840 Basic Crucial MX200 Windows 10 HP IPS 23-inch 1080p 
PowerCaseMouse
Be quiet! BN144 System Power 7 500W NZXT Tempest 210 Zowie FK1 
  hide details  
Reply
2013
(11 items)
 
  
CPUMotherboardGraphicsRAM
Intel Core i7 2700k GIGABYTE GA-Z77-D3H Sapphire HD 7870 XT Boost G.SKILL 1866 Sniper 2x4 GB 
Hard DriveHard DriveOSMonitor
Samsung 840 Basic Crucial MX200 Windows 10 HP IPS 23-inch 1080p 
PowerCaseMouse
Be quiet! BN144 System Power 7 500W NZXT Tempest 210 Zowie FK1 
  hide details  
Reply
post #7 of 10
I figured it out...here the the jsfiddle...

http://sqlfiddle.com/#!9/13a42/18
Code:
SELECT COUNT(DISTINCT t.Faculty_ID)
FROM IS_Qualified_t t
WHERE t.Faculty_ID IN 
(SELECT l.Faculty_ID FROM IS_Qualified_t l
 GROUP BY l.Faculty_ID 
 HAVING COUNT(l.Course_ID) >2)

Edited by surfbumb - 3/27/15 at 5:50pm
Black Silence
(15 items)
 
  
CPUMotherboardRAMHard Drive
i5 3570k @ 4.5 GHz Asus P8Z77-M Pro Kingston HyperX Genesis 8 GB - 1600 MHz Seagate Barracuda 250 GB 
Optical DriveCoolingOSMonitor
Samsung WriteMaster Noctua NH-D14 Windows 10 ASUS VS24AH-P 
KeyboardPowerCaseMouse
Logitech Navigator Enermax Infiniti 650W Fractal R3 Black Pearl Razer Death Adder 
Mouse PadAudio
SteelSeries QcK Mass Altec Lansing FX4021 
  hide details  
Reply
Black Silence
(15 items)
 
  
CPUMotherboardRAMHard Drive
i5 3570k @ 4.5 GHz Asus P8Z77-M Pro Kingston HyperX Genesis 8 GB - 1600 MHz Seagate Barracuda 250 GB 
Optical DriveCoolingOSMonitor
Samsung WriteMaster Noctua NH-D14 Windows 10 ASUS VS24AH-P 
KeyboardPowerCaseMouse
Logitech Navigator Enermax Infiniti 650W Fractal R3 Black Pearl Razer Death Adder 
Mouse PadAudio
SteelSeries QcK Mass Altec Lansing FX4021 
  hide details  
Reply
post #8 of 10
Hmmm.... looks a bit too complex...
Code:
select * from (
  select Faculty_ID, count(*) as total from IS_Qualified_t  
  group by Faculty_ID 
) x
where total > 2
Ol' Sandy
(28 items)
 
"Zeus"
(12 items)
 
Elite Preview
(6 items)
 
CPUMotherboardGraphicsRAM
Intel Xeon E3-1230v3 Gigabyte GA-Z97X-UD5H-BK MSI Gaming GTX 980 Kingston 32GB (4x8) 
Hard DriveHard DriveHard DriveHard Drive
Plextor PX-256M5S 256GB Samsung EVO 1TB Hitachi HDS721010CLA332 Hitachi HDS723020BLA642 
Hard DriveHard DriveHard DriveOptical Drive
Hitachi HDS723020BLA642 Hitachi HUA722010CLA330 WDC WD10EARS-00Z5B1 TSSTcorp CDDVDW SH-S223B 
CoolingCoolingOSMonitor
Phanteks PH-TC14PE with TY-140's Lamptron FCv5 (x2) Windows 8 Pro 64-bit Dell U2412M 
MonitorMonitorMonitorKeyboard
Dell U2412M Dell U2212HM Dell U2713HM Topre Realforce 87UB | Ducky DK9087 G2 Pro 
PowerCaseMouseMouse Pad
Corsair AX-750 Corsair Obsidian 650D Logitech G700 XTRAC Ripper XXL 
AudioAudioAudioAudio
Beyerdynamic DT-770 Pro 250ohm Schiit Bifrost DAC Schiit Asgard 2 HiVi Swan M50W 2.1 
CPUMotherboardRAMHard Drive
Intel Xeon E5-2620 Super Micro X9SRL-F-B 128GB 1333MHz LSI 9271-8i 
OSPowerCase
VMware ESXi 5.5 SeaSonic SS-400FL2 Fractal Define R3 
CPUMotherboardGraphicsRAM
Intel Core i5-3437U HP EliteBook Folio 9470m  Intel HD Graphics 4000  16GB DDR3 SDRAM 
Hard DriveOS
256GB SSD Windows 10 Insider Preview 
  hide details  
Reply
Ol' Sandy
(28 items)
 
"Zeus"
(12 items)
 
Elite Preview
(6 items)
 
CPUMotherboardGraphicsRAM
Intel Xeon E3-1230v3 Gigabyte GA-Z97X-UD5H-BK MSI Gaming GTX 980 Kingston 32GB (4x8) 
Hard DriveHard DriveHard DriveHard Drive
Plextor PX-256M5S 256GB Samsung EVO 1TB Hitachi HDS721010CLA332 Hitachi HDS723020BLA642 
Hard DriveHard DriveHard DriveOptical Drive
Hitachi HDS723020BLA642 Hitachi HUA722010CLA330 WDC WD10EARS-00Z5B1 TSSTcorp CDDVDW SH-S223B 
CoolingCoolingOSMonitor
Phanteks PH-TC14PE with TY-140's Lamptron FCv5 (x2) Windows 8 Pro 64-bit Dell U2412M 
MonitorMonitorMonitorKeyboard
Dell U2412M Dell U2212HM Dell U2713HM Topre Realforce 87UB | Ducky DK9087 G2 Pro 
PowerCaseMouseMouse Pad
Corsair AX-750 Corsair Obsidian 650D Logitech G700 XTRAC Ripper XXL 
AudioAudioAudioAudio
Beyerdynamic DT-770 Pro 250ohm Schiit Bifrost DAC Schiit Asgard 2 HiVi Swan M50W 2.1 
CPUMotherboardRAMHard Drive
Intel Xeon E5-2620 Super Micro X9SRL-F-B 128GB 1333MHz LSI 9271-8i 
OSPowerCase
VMware ESXi 5.5 SeaSonic SS-400FL2 Fractal Define R3 
CPUMotherboardGraphicsRAM
Intel Core i5-3437U HP EliteBook Folio 9470m  Intel HD Graphics 4000  16GB DDR3 SDRAM 
Hard DriveOS
256GB SSD Windows 10 Insider Preview 
  hide details  
Reply
post #9 of 10
Or even:
Code:
  select count(*) from
  (
    select Faculty_ID, count(*) as total from IS_Qualified_t  
    group by Faculty_ID 
    having total > 2
  ) a

EDIT: OP actually wanted just the count, so updated accordingly. Lots of ways to achieve the same thing.
Edited by randomizer - 3/28/15 at 1:09am
    
CPUMotherboardGraphicsRAM
i7 920 D0 MSI X58 Pro-E GTX 560 Ti 448 3x2GB G.Skill DDR3-1333 9-9-9-24 
Hard DriveHard DriveOptical DriveOS
840 Pro Caviar Black LG BD-ROM Windows 8.1 Pro x64 
MonitorMonitorKeyboardPower
Dell U2713HM Dell U2311H Turbo-Trak (Google it :D) Corsair HX-520 
CaseMouseMouse PadAudio
CM690 Mionix Avior 7000 Everglide Titan AKG K 242 HD 
  hide details  
Reply
    
CPUMotherboardGraphicsRAM
i7 920 D0 MSI X58 Pro-E GTX 560 Ti 448 3x2GB G.Skill DDR3-1333 9-9-9-24 
Hard DriveHard DriveOptical DriveOS
840 Pro Caviar Black LG BD-ROM Windows 8.1 Pro x64 
MonitorMonitorKeyboardPower
Dell U2713HM Dell U2311H Turbo-Trak (Google it :D) Corsair HX-520 
CaseMouseMouse PadAudio
CM690 Mionix Avior 7000 Everglide Titan AKG K 242 HD 
  hide details  
Reply
post #10 of 10
Thread Starter 
Sorry for the late reply but thanks a lot for the help guys. Looks like querying a sub query is the way to go and logically makes sense. I spoke with my instructor briefly via email and she said that there is actually an easier way to get the answer instead of using a nested query and that she'll explain it in class this week. Thanks again!
Edited by NitrousX - 3/30/15 at 9:16am
Destructor
(28 items)
 
  
CPUMotherboardGraphicsRAM
Intel i7-3770K @ 4.6GHz  Gigabyte G1.Sniper 3  EVGA GTX 980 Ti SC+  16GB Corsair Dominator Platinum 2400MHz  
Hard DriveHard DriveHard DriveOptical Drive
Samsung 850 Pro 256GB RAID 0 Samsung F3 1TB Seagate 3TB LG Blu-ray 12x 
CoolingOSMonitorKeyboard
XSPC RayStorm D5 RX360  Windows 10 Pro x64 LG 24GM77 Ducky Shine 3 Year of the Snake 
PowerCaseMouseMouse Pad
Corsair HX850 Corsair 900D  Logitech G400  Razer Goliathus Speed  
AudioAudio
Sound Blaster Zx  Sennheiser HD 558 + Zm-Mic1 
  hide details  
Reply
Destructor
(28 items)
 
  
CPUMotherboardGraphicsRAM
Intel i7-3770K @ 4.6GHz  Gigabyte G1.Sniper 3  EVGA GTX 980 Ti SC+  16GB Corsair Dominator Platinum 2400MHz  
Hard DriveHard DriveHard DriveOptical Drive
Samsung 850 Pro 256GB RAID 0 Samsung F3 1TB Seagate 3TB LG Blu-ray 12x 
CoolingOSMonitorKeyboard
XSPC RayStorm D5 RX360  Windows 10 Pro x64 LG 24GM77 Ducky Shine 3 Year of the Snake 
PowerCaseMouseMouse Pad
Corsair HX850 Corsair 900D  Logitech G400  Razer Goliathus Speed  
AudioAudio
Sound Blaster Zx  Sennheiser HD 558 + Zm-Mic1 
  hide details  
Reply
New Posts  All Forums:Forum Nav:
  Return Home
  Back to Forum: Coding and Programming
Overclock.net › Forums › Software, Programming and Coding › Coding and Programming › SQL Counting Question