Using COUNT with JOINS…

Whenever we use the COUNT function, we invariably use * as an argument to it.   It works fine as long as its not joined with another tables.   For example, in SQL Sever Pubs database, there are two tables – jobs and employee. If we need to find the number of employees for each job, the following query will not work

SELECT jobs.job_DESC
 ,count(*)
FROM JOBS
LEFT JOIN EMPLOYEE
       ON JOBS.JOB_ID = EMPLOYEE.JOB_ID
GROUP BY JOBS.JOB_DESC
ORDER BY 2

The records returned by above query

Business Operations Manager                        1
Chief Executive Officer                            1
Chief Financial Officier                           1
New Hire – Job not specified                       1
Designer                                           3
Editor                                             3
Sales Representative                               3
Acquisitions Manager                               4
Managing Editor                                    4
Marketing Manager                                  4
Operations Manager                                 4
Productions Manager                                4
Public Relations Manager                           4
Publisher                                          7

But in database, the job "New Hire – Job not specified" has no records in employee.  So, it should zero.

After doing some research, I found how silly I am.  The correct query will be
SELECT jobs.job_DESC
 ,count(EMPLOYEE.JOB_ID)
FROM JOBS
LEFT JOIN EMPLOYEE
       ON JOBS.JOB_ID = EMPLOYEE.JOB_ID
GROUP BY JOBS.JOB_DESC
ORDER BY 2

The results of this query is

New Hire – Job not specified                       0
Business Operations Manager                        1
Chief Executive Officer                            1
Chief Financial Officier                           1
Designer                                           3
Editor                                             3
Sales Representative                               3
Acquisitions Manager                               4
Managing Editor                                    4
Marketing Manager                                  4
Operations Manager                                 4
Productions Manager                                4
Public Relations Manager                           4
Publisher                                          7

Then, I went to books online and checked the argument list for COUT… it clearly states how the COUNT works..

COUNT(*) returns the number of items in a group, including NULL values and duplicates.

COUNT(ALL expression) evaluates expression for each row in a group and returns the number of nonnull values.

COUNT(DISTINCT expression) evaluates expression for each row in a group and returns the number of unique, nonnull values.

A simple thing, but today it took me sometime to figure this out.  Good that I learnt this atleast now.  I then thought how else can I achieve the above results by giving * as the argument to COUNT (but then, these are only possibilities and not efficient ways).   It can be achieved by using Correlated sub queries and Union operation.

Correlated subquery

SELECT job_Desc
 ,(select count(*) from employee where employee.job_id = jobs.job_id) as count
FROM Jobs
ORDER BY 2

UNION operation

SELECT jobs.job_DESC
 ,count(EMPLOYEE.JOB_ID)
FROM JOBS
JOIN EMPLOYEE
       ON JOBS.JOB_ID = EMPLOYEE.JOB_ID
GROUP BY JOBS.JOB_DESC
UNION
SELECT job_desc, 0
FROM jobs
WHERE job_id NOT in (select job_id from EMPLOYEE)
ORDER BY 2

Three different (may be more) ways to achieve a result. Certainly one is better than other two.  No wonder why we programmers write bad code…     

Advertisement

Teched 2006 Day 2 (16-Jun-2006)

Today is my second day in TechEd 2006.  Today, I was very selective in choosing the track.   So, fairly a good day but for one session.  

Session 1:  Windows Power Shell : The next generation of Scripting  

 A good session on introduction to power shell.  The concept is very good.  Its built up on alias concept.   Now you can use unix like commands on powershell. The Microsoft says  its for administrators to switch between windows and other operating systems easily and they can use the same commands to achieve the tasks.  Power scritping is based on .NET2.0.  Powershell is based on OO concept.  Power scripts view File system as objects, registry as objects, process as object.  This means that you can use “dir” on file system, “dir” on process, “dir” on registry.  The speaker introduced to powershell was really good.

Session 2:  Introduction to Windows Workflow Foundation – WWF

A good session.  Its about workflow.  Basics of workflow was covered.  A good session.

Session 3:  Sharepoint 2007 for developers.

 A disappointing session. Speaker was late.  He rushed in just browsed through a few pages. I don’t know why they had this session in their itenary.  A flash demo on website is enough to cover this… 

 Session 4:  Developer Productivity with VSTS: Automated Unit testing

A good seesion. The speaker covered about unit testing, code coverage etc. Though they werent deep enough, it was a good introduction.  The speaker spent a few minutes on VSTS features and focused on unit testing.  Microsoft has now realized they cannot get away from agile.  The speaker even touched about TDD and Agile.

Session 5:  BizTalk 2006 Whats in for a Developer.

 An Excellent session. The speaker gave introduction go Biztalk taking a case study. He talked  about

• EAI  -Enterprise Application Integration
• B2B – Business to Business
• Business Work flow and porcess control

He touched about the following

• Orchestration
• Pipeline
• Adapter
• Business Rules Engine.

 Finally we had some entertainment session.  They conducted a session called  “15 seconds to Fame” in the afternoon. The eight people who entered the finals were contesting for a smart phone and one guy walked away with samrt phone.

To sum up,  given the experience, I will attend the next TechEd.  Even if the company doesn’t pay me, I am planning to go for it.   But the next time,I need to be very selective in the sessions to attend. A good eye opener.  

Agile Chennai User Group Kickoff on 8th July 2006

I am very much fasciated by Agile.  Chennai for a long time didnt have an user group on Agile.  I am now in the process of forming an User Group.  The idea is the bring together a group of like minded people and meet regulary and learn more about Agile.

 We are having a Kick off meeting on 8th July 2006 with a presentation by Sanjiv Augustine.  We are in the process of finalizing the venue.  I will post another message once the venue is fixed.

Teched 2006 Day 1 (15-Jun-2006)

Today I went to Teched 2006.   Its a two day event and  it was held in Chennai Trade Center.

I attended the Developer track.  Its fairly a good day.  Of  the five I attended today, I didnt like 2.   I feel that two were more towards marketing the product and so not very informative.   Here are the details

Session 1:  Extending the ASP.NET Provider Model  

The speaker spoke about Membership provider model.  Though the concept is good one, I am not sure how it will be applied for building Enterprise applications.   The speaker was signing the marketing song.. “We didnt even write a single line of code.. we have done so many things..”.   In my 7 years of experience,  I havent written a full fledged application  using the tool features as trumpeted by MS.   I dont wish to go in detail in this.

Session 2:  Ajax for ASP.NET Developers – Atlas.net

A good session. The speaker covered the various programming models in Atlas – Server Side and Client Side.  She had some interesting demos.  She also showed how to write Ajax based application using xml and how its taken care of by Atlas java scripts.  She spoke about various server side Ajax based extenders / controls.   This is the second or third time, I am listening to the ATLAS seminar conducted by Microsoft.   It was informative.

Session 3:  Using CLR inside SQL Server: Best PracticesA good session. 

The speaker touched about the various topics on where should C# code to be used and where not to be used.  How CLR memory is loaded.  What the security aspects.   He described the various scenarios where the CLR code should be used. Basically, he is suggesting that C# code should be used where TSQL cannot achieve things.  Example would be to access a webservice from database, to write user defined functions etc.  Speaker was very knowledgeable and a good session.

Session 4:  A peep at the Future .NET LAnguages : LINQ

This is the best presentation of the day and I liked it very much.  A great presentation.  The speaker went into every minute details of the LINQ.  Other speakers, if you read my blog, take a note of this.  We look for this kind of presentations.  We pay  money to hear from experts like the guy who presented this.   Definitely we not interested in your marketing stuff – ” we did this with one line of code .. we did this with 3 lines of code” etc.   An excellent presentation.  The way he presented showed his experience in LING.  A great show.

Session 5:  Microsoft Visual Studio 2005 Team System: Enterprise Class Source Control.

This didnt influence me.  I am currently using a Borland product which has features more than what speaker spoke about.   Another session similar in the lines of first session. No comments.  Teched guys, please stop this kind of marketing stuff.
Disclaimer : The thoughts expressed here are purely of mine.  This doesnt reflect the thoughts of any one.

 Finally we had Demo shoot out.  They asked audience to come and present the demo.  A couple of them were really very informative. 

 An overall good day. Lets see how tomorrow is going to be.

Basics of Object Modelling…

Whats Object Modelling?
Object modelling is about creating a blue print of the software system that is to be constructed from objects.

 There are many object modelling methodologies.  Typically a modelling methodology consists of following three components
1.  Process  :  The how to steps for gathering requirements and determining the abstraction to be modelled.  There are many proceses out there. The one which has wider acceptance is Rational Unified Process – RUP.  RUP is  a software development methodology that contains analysis, design, project management, configuration & change management.
2. Notation :   A graphical language to represent the model.  There are many standards available for representing the model in graphical notations. A couple of them are 
                            OMT – Object Modelling Technique (James Rumbaugh)
                            Booch Method – Grady Booch
                            Use Case as means for finalizing Requirements – Ivar Jabcobson.
                            UML – Industry Standard. (The above three guys contributed                      much to this standard)
3. Tool :  A tool to help us to build the model.

Requirement Analysis using – "Use Case Modelling".

USE CASE  is a narrative or graphical represenetation of a particular goal of a system.  ie, the representation of
(1) who are all the users of the system,
(2) what services should the system provide 
(3)  when teh user(s) interacts with the system for a particular purpose, what is the desired outcome.

Note:
1. Use Cases capture only the functional requirements (like purpose of the system, users, look and feel  etc.) of the system. ie goals of the system. They dont capture the technical requirements  (like how the system should be implemented, whether it will use a particular technology etc).

Actors :  Actors are the users of the system after its build.  They can be end users, a computer system or anything.   Note that a particular user can play multiple roles- example John can be a regular user of the system and can also be administartor of the system. In this case, there are two actors – regular user and administartive user. So, dont confuse actors with physical person.  You need to identify all users(and roles) of the system.  Once its identified, it can be represented in a graphical format using UML like below.  Actors are represented as a sticky figure, the use case by an oval and the system boundry is represented  by a rectange.

Note that there can be arrow heads in the line that connects the actor and the system.  An Actor can provide information to the system, can consume information from the system or do both.

A use case represents a logical series of events begining with Actor's first contact with the System and ending with the achivement of actors goal of using the system.  In a Use Case, an actor always initiates the process.The use case shown above is a highlevel one.  It can be decomposed based on the system. For smaller systems, there can be usecase for each module, a larger system there can be an usecase for each functionality. 

Class Diagrams

Classes identified can be classified like
1. Domain Classes – That represent the system
2. Implementation Classes – These classes are doesnt represent the system, but are required for implementation. For example collection class is an implementation class
3. Association Class – A class used to associate two classes.  For example if there is a many to many relationship between two classe, then a third class called association class is introduced to make the relationship. (just like data modelling, we introduce a third table to refer a many to many relationship) 

SEQUENCE DIAGRAM

Sequence diagrams are UML diagrams for representing various scenarios of the system.

Graphical representation of how message should flow  from one object to another in carring out a given scenario.