• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 352
  • Last Modified:

sql synax

I have attached excel and please assume it is just a database table in sql server 2010.
I know how to write the sql syntax to group them to show as attachment except the one showing 0 premium.

When the premium is zero (AgencyId sis 1003 in this case), this line is always disappeared.
I just want to have result set exactly like attachment.

I did try to use left join but it still does not work. Please help
Sample.xlsx
0
Webboy2008
Asked:
Webboy2008
  • 10
  • 9
  • 5
  • +3
1 Solution
 
dqmqCommented:
Left Join to what?

Please reveal your SQL that is not working
0
 
Manoj PatilSr. Software EngineerCommented:
Are these columns in 2 different tables ???
0
 
Chris LuttrellSenior Database ArchitectCommented:
Did you try something like this:
Select t2.Premium, t1.AgencyId
From AgencyTable t1
LEFT OUTER JOIN PremimumTable t2 ON t1.AgencyId = t2.AgencyId
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Patrick TallaricoFSEP Systems AnalystCommented:
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 ...
0
 
Webboy2008Author Commented:
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
0
 
Patrick TallaricoFSEP Systems AnalystCommented:
Can you include the SQL you are currently using for this?
0
 
Webboy2008Author Commented:
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
0
 
dqmqCommented:
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.
0
 
Webboy2008Author Commented:
Please check my desire result from my original attached. Sample.xlsx from my question
0
 
Webboy2008Author Commented:
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
0
 
Chris LuttrellSenior Database ArchitectCommented:
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;
0
 
Chris LuttrellSenior Database ArchitectCommented:
Or here is your database back with your query corrected to return all 5 Agencies.
Database1.accdb
0
 
Webboy2008Author Commented:
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).
0
 
dqmqCommented:
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;
0
 
Webboy2008Author Commented:
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
0
 
dqmqCommented:
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;
0
 
Chris LuttrellSenior Database ArchitectCommented:
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
0
 
dqmqCommented:
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.
0
 
Webboy2008Author Commented:
dqmq: It is different.
0
 
Chris LuttrellSenior Database ArchitectCommented:
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;
0
 
dqmqCommented:
I am saying that changing the datatype of AgencyID, does not change my answer.  It works either way.
0
 
Webboy2008Author Commented:
I got alert message when i try to run your query.
-wrong number of argument used with function in query expression isnull(...),0
0
 
Webboy2008Author Commented:
dqmq: Just download the latest access file and you will know what i mean.
0
 
dqmqCommented:
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;

Open 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;

Open in new window

0
 
Manoj PatilSr. Software EngineerCommented:
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

Output as you want !!!
0
 
Webboy2008Author Commented:
My question is to show up agencyid 5, even the transaction table does not have agencyid 5 there.
0
 
dqmqCommented:
The SQL I've given you returns exactly those results.  Please try it.  If the results are not satisfactory, then please explain.
0
 
Anthony PerkinsCommented:
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?
0
 
dqmqCommented:
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.
0
 
Anthony PerkinsCommented:
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...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 10
  • 9
  • 5
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now