Where clause in count, and joining two queries

Databases
1

I have a table that I am trying to count the number of course passed, and also list the modules passed as well.

the first problem I am having is what to put in the where variable , so that its not specific to a customer(I can use the query below for a particular customer and a particular course)but I will like a generic query where the result will be distinct in terms of user and course like the one below

select FirstName,LastName,CourseTitle,Noofmodules, count (Coursecompleted) as modulescompleted from EStudentsprogress where Coursecompleted = '1'and EmailAddress = 'scascsc@e.co.uk'
and CourseTitle = 'Microsoft MOS 2010 EXCEL' Group by FirstName, LastName, CourseTitle, Noofmodules ;

How can i make it list the result as above, whereby i dont specify the email address or course title(trying to get the result for all the clients )

. Also I have a query that list the courses that is passed by the customer, I will like the column with the list of courses passed be added to the result above, but as a column for each course.

select FirstName,LastName,CourseTitle, EmailAddress, CourseModule as coursepassed from EStudentsprogress where coursecompleted =1

cheeres

2

The easiest way to do that would be to build the query dynamically in the server side language of your choosing. You CAN do it in SQL by using LIKE, but the performance hit would be ridiculous.

3

i used is not null for the first query and it seems to work,

select FirstName,LastName,CourseTitle,Noofmodules, count (Coursecompleted) as modulescompleted from EStudentsprogress where Coursecompleted = '1'and EmailAddress IS NOT NULL
and CourseTitle IS NOT NULL Group by FirstName, LastName, CourseTitle, Noofmodules ;

but i still have to verify it for each user now to be sure its ok

4

All that really does is verify that there is a value in the fields.

5

i dont know why, its doing the work for the first query

6

Not really. In the first query, it’s only pulling the records that have EmailAddress = ‘scascsc@e.co.uk’ and CourseTitle = ‘Microsoft MOS 2010 EXCEL’

In the new one, it doesn’t matter what value is in EmailAddress and CourseTitle. As long as neither are blank, it pulls the records. Either one could be a single space and it would be returned. As long as you’re handling the resultset accordingly, you’ll be fine. Just be aware of what you’re getting back.

7

I think you could just remove email address and course title from the WHERE clause.

And it also looks like your database could use some normalisation :wink:

8

Thanks. It was resolved using stuff function, (though I do not really understand it fully)

SELECT        FirstName, LastName,EmailAddress, CourseTitle, lastlogindate, Noofmodules, COUNT(Coursecompleted) AS modulesstarted, REPLACE(REPLACE(REPLACE('<' + STUFF
                             ((SELECT        ',' + CAST(CourseModule AS varchar(20)) AS Expr1
                                 FROM            EDSF1
                                 WHERE        (FirstName = e.FirstName) AND (LastName = e.LastName) AND (Coursecompleted = '1') AND (CourseTitle = e.CourseTitle) FOR XML PATH('')), 1, 1, ''), 
                         '<Expr1>', ''), '</Expr1>', ''), ',', '') AS CoursesCompleted
FROM            EDSF1 AS e
WHERE        (Coursecompleted = '1') OR
                         (Coursecompleted = '0')
GROUP BY FirstName, LastName,EmailAddress, CourseTitle, lastlogindate, Noofmodules
9

when you resolve something by implementing a solution you got from the internet, without understanding how it works, you’re just setting yourself up for more trouble later