SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL query help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mamzy1990
Starting Member

United Kingdom
2 Posts

Posted - 06/27/2013 :  04:36:33  Show Profile  Reply with Quote
Hey guys i need to create a query were i can find out the number of learners there are with a model of 21 and another model eg

learner ---------- model
mamzy ------------ 20
mamzy ------------ 21
mamzy ------------ 30
jack ------------- 21
jack ------------- 99
greg ------------- 99
greg ------------- 30
harper ----------- 20
mully ------------ 21

RESULT

LEARNER ---------- model
mamzy ------------ 20
mamzy ------------ 21
mamzy ------------ 30
jack ------------- 21
jack ------------- 99

please help

ive tried

SELECT *
FROM tablename alias_allRecords
WHERE EXISTS (SELECT 1
FROM tablename alias_21
WHERE alias_21.model = 21 AND alias_allRecords.learner = alias_21.learner);

but i get a long list as the first execution please help!!!!!!!!

mamzy rahman

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/27/2013 :  05:10:48  Show Profile  Reply with Quote

SELECT LEARNER,model
FROM (SELECT COUNT(CASE WHEN model=21 THEN 1 END) OVER (PARTITION BY LEARNER) AS [Mod21Cnt],
             COUNT(CASE WHEN model<>21 THEN 1 END) OVER (PARTITION BY LEARNER) AS [ModOtrCnt], *
      FROM Table
      )t
WHERE [Mod21Cnt] > 0
AND [ModOtrCnt] > 0


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

mamzy1990
Starting Member

United Kingdom
2 Posts

Posted - 06/27/2013 :  05:15:58  Show Profile  Reply with Quote
hey thanks for the help but i get this error
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'OVER'.

im an accountant and the sql boys are not in today and really need to egt this data out pls help

mamzy rahman
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/27/2013 :  05:19:30  Show Profile  Reply with Quote
are you using sql 2000? OVER syntax will work only from sql 2005 onwards

try this alternative which will work even in sql 2000

SELECT t.LEARNER,t.model
FROM Table t
INNER JOIN(SELECT LEARNER,
                  COUNT(CASE WHEN model=21 THEN 1 END) AS [Mod21Cnt],
                  COUNT(CASE WHEN model<>21 THEN 1 END) AS [ModOtrCnt]
            FROM Table
            GROUP BY LEARNER
          )t1
ON t1.LEARNER = t.LEARNER
WHERE [Mod21Cnt] > 0
AND [ModOtrCnt] > 0


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

abbyyy
Starting Member

9 Posts

Posted - 06/28/2013 :  00:07:48  Show Profile  Reply with Quote
unspammed provides a variety of query design tools that you can use to create dataset queries in Report Designer. The type of data source that you are working with determines the availability of a particular query designer. In addition, some query designers provide alternate modes so that you can choose whether to work in visual mode or directly in the query language.
Go to Top of Page

sshelper
Posting Yak Master

216 Posts

Posted - 07/01/2013 :  01:20:18  Show Profile  Visit sshelper's Homepage  Reply with Quote
You can also try this:

SELECT A.*
FROM [dbo].[YourTable] A INNER JOIN (SELECT [Learner] FROM [dbo].[YourTable] GROUP BY [Learner] HAVING COUNT(*) > 1) B
ON A.[Learner] = B.[Learner] INNER JOIN (SELECT [Learner] FROM [dbo].[YourTable] WHERE [Model] = 21) C ON A.[Learner] = C.[Learner]

SQL Server Helper
http://www.sql-server-helper.com/tips/tip-of-the-day.aspx?tkey=8ca5e977-63fa-43bf-8803-fced19f429e5&tid=92&tkw=sql-server-database-design---twitter-profile-and-followers

Edited by - sshelper on 07/01/2013 01:23:51
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000