SQL query help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL query help

Author

Topic

mamzy1990
Starting Member

2 Posts

Posted - 2013-06-27 : 04:36:33

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

52326 Posts

Posted - 2013-06-27 : 05:10:48

[code]
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
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

mamzy1990
Starting Member

2 Posts

Posted - 2013-06-27 : 05:15:58

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-27 : 05:19:30

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

abbyyy
Starting Member

9 Posts

Posted - 2013-06-28 : 00:07:48

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.

sshelper
Posting Yak Master

216 Posts

Posted - 2013-07-01 : 01:20:18

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources