sql synax

Left Join to what?

Please reveal your SQL that is not working

Are these columns in 2 different tables ???

Did you try something like this:
Select t2.Premium, t1.AgencyId
From AgencyTable t1
LEFT OUTER JOIN PremimumTable t2 ON t1.AgencyId = t2.AgencyId

I am assuming that there is no reference to the AgencyID in the Premium table for the join to link on to
you could try to set the premium field to an explicit value for cases where it is null...

Select (
 CASE
          WHEN Premium IS NULL THEN '0'
          ELSE Premium
     END), as 'Premium',
     AgencyID
from ...

I have one associated table called AgencyName(AgencyName is  the column name) and have key agencyId

I need to get working sql syntax if possible. thank

Can you include the SQL you are currently using for this?

I attached access database for your review. All i want is to get all of agencyId regardless of the premium from 0 to any numbers.
Database1.accdb

I'm sorry, I don't understand your issue. I have downloaded your database and there is nothing inherent that suppresses agencyId's with zero premium.  Whatever is causing that problem is in your SQL, which for some reason you don't want to reveal.

Perhaps we can approach it this way.  Given the tables in your example database, show us the results in table form that you want to see.

Please check my desire result from my original attached. Sample.xlsx from my question

Check this attached. I added query there. The query is working but what I really want to see is Agency E (AgencyId=5). even it does not exist in the transaction table.
Database1.accdb

Try this query.  I just ran it against your DB and Agency E shows up.

SELECT Agency.AgencyId, Agency.AgencyName, Sum(Transactions.Premium) AS Premium
FROM Agency LEFT JOIN Transactions ON Agency.AgencyId = Transactions.AgencyId
GROUP BY Agency.AgencyId, Agency.AgencyName;

Or here is your database back with your query corrected to return all 5 Agencies.
Database1.accdb

i added the query in the sql server to test, and it does not show the agencies (the last one without any trans in transactions table).

No problem:

SELECT Sum(nz(Transactions.Premium,0)) AS SumOfPremium, Agency.AgencyId
FROM Agency LEFT JOIN Transactions ON Agency.AgencyId = Transactions.AgencyId
GROUP BY Agency.AgencyId;

my bad. please use this attached db. the agencyId is actually text, not number, and now the query does not show the last agency...
Database1.accdb

In SQL Server, the equivalent is this:

SELECT Sum(ISNULL(Transactions.Premium,0)) AS SumOfPremium, Agency.AgencyId
FROM Agency LEFT JOIN Transactions ON Agency.AgencyId = Transactions.AgencyId
GROUP BY Agency.AgencyId;

Did it work in the Access Database? It did for me when I ran it.
What is your exact query you are running in the real SQL Server?  You must have some syntax incorrect.
Sample-Query-Results.png

The query is the same regardless of whether AgencyID is text or number.   The important part to return the Agencies not represented in the Transaction table is to us an Outer (Left in this case) join.

dqmq: It is different.

Are you not getting the row back or is the value for SumOfPremium still null?

If the later, try this
SELECT ISNULL(Sum(Transactions.Premium),0) AS SumOfPremium, Agency.AgencyId
FROM Agency LEFT JOIN Transactions ON Agency.AgencyId = Transactions.AgencyId
GROUP BY Agency.AgencyId;

I am saying that changing the datatype of AgencyID, does not change my answer.  It works either way.

I got alert message when i try to run your query.
-wrong number of argument used with function in query expression isnull(...),0

dqmq: Just download the latest access file and you will know what i mean.

I did.  And the datatype does not affect the query, nor my answer.  The reason you got the "wrong number of arguments" message  is because you ran the SQL server query on Access.  

Here's the the Access query:

SELECT Sum(nz(Transactions.Premium,0)) AS SumOfPremium, Agency.AgencyId
FROM Agency LEFT JOIN Transactions ON Agency.AgencyId = Transactions.AgencyId
GROUP BY Agency.AgencyId;

Select allOpen in new window

Here's the equivalent SQL Server query:

SELECT Sum(isnull(Transactions.Premium,0)) AS SumOfPremium, Agency.AgencyId
FROM Agency LEFT JOIN Transactions ON Agency.AgencyId = Transactions.AgencyId
GROUP BY Agency.AgencyId;

Select allOpen in new window

Hi update your query as follows

SELECT Sum(Transactions.Premium) AS SumOfPremium, Agency.AgencyId
FROM Agency RIGHT JOIN Transactions ON Agency.AgencyId = Transactions.AgencyId
GROUP BY Agency.AgencyId;

See Output Below

My question is to show up agencyid 5, even the transaction table does not have agencyid 5 there.

The SQL I've given you returns exactly those results.
I am still confused as to what SQL dialect the author needs.  Is it T-SQL or the SQL dialect used by MS Access as you propose in your solution?  
When the author stated:

-wrong number of argument used with function in query expression isnull(...),0

Whose solution were they referring to?

Agreed, confusion reigns.  The author delivers an Access sandbox to us, but then claims to have loaded the query into SQL Server for testing.  What's an expert to do? I offered equivalent solutions in both dialects and also noted that the author's error was caused by using the wrong one.  It's frustrating because on the scale of difficulty this problem barely makes it on the chart and here we are 29 comments later.

This "smells" like one of those cases where for the sake of simplification or privacy we are given a contrived situation and our solutions are not being applied correctly to the real one.  I dunno.

I could not agree more.  It is a perfectly trivial problem that has not been explained correctly.  If the author really wanted an answer they should have done the following:
1. Provide a script to create the table(s).
2. A script to populate the data.
3. The output required using that data.
That is it.  It is not difficult.  You do not even have to explain it in English.

What they did was provide an Excel spreadsheet with the following data and with the clarification that we should "assume it is just a database table in sql server 2010" (The fact that "sql server 2010" never existed should have been a warning):
Premium      AgencyId
5000      1000
2000      1001
10000      1002
0      1003

Then we are told that there is "one associated table called AgencyName(AgencyName is  the column name) and have key agencyId"  Whatever that means...

They then attach an MS Access database.

Then we get another MS Access database and "I added query there. The query is working but what I really want to see is Agency E (AgencyId=5). even it does not exist in the transaction table."

And then a third one with the note that "the agencyId is actually text, not number, and now the query does not show the last agency..."

Then we get the cryptic "dqmq: It is different."  What is different?

And then "I got alert message when i try to run your query."  in response to what?

And so and so on...