Solved

sql synax

Posted on 2012-04-04
30
342 Views
Last Modified: 2012-04-11
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
Comment
Question by:Webboy2008
  • 10
  • 9
  • 5
  • +3
30 Comments
 
LVL 42

Expert Comment

by:dqmq
ID: 37808470
Left Join to what?

Please reveal your SQL that is not working
0
 
LVL 19

Expert Comment

by:Manoj Patil
ID: 37808567
Are these columns in 2 different tables ???
0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 37808607
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
 
LVL 6

Expert Comment

by:Patrick Tallarico
ID: 37808648
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
 

Author Comment

by:Webboy2008
ID: 37808742
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
 
LVL 6

Expert Comment

by:Patrick Tallarico
ID: 37808759
Can you include the SQL you are currently using for this?
0
 

Author Comment

by:Webboy2008
ID: 37808890
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
 
LVL 42

Expert Comment

by:dqmq
ID: 37808940
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
 

Author Comment

by:Webboy2008
ID: 37808951
Please check my desire result from my original attached. Sample.xlsx from my question
0
 

Author Comment

by:Webboy2008
ID: 37808969
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
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 37808974
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
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 37808988
Or here is your database back with your query corrected to return all 5 Agencies.
Database1.accdb
0
 

Author Comment

by:Webboy2008
ID: 37809013
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
 
LVL 42

Expert Comment

by:dqmq
ID: 37809025
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
 

Author Comment

by:Webboy2008
ID: 37809034
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 42

Expert Comment

by:dqmq
ID: 37809036
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
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 37809039
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
 
LVL 42

Expert Comment

by:dqmq
ID: 37809050
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
 

Author Comment

by:Webboy2008
ID: 37809057
dqmq: It is different.
0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 37809062
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
 
LVL 42

Expert Comment

by:dqmq
ID: 37809065
I am saying that changing the datatype of AgencyID, does not change my answer.  It works either way.
0
 

Author Comment

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

Author Comment

by:Webboy2008
ID: 37809078
dqmq: Just download the latest access file and you will know what i mean.
0
 
LVL 42

Expert Comment

by:dqmq
ID: 37809440
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
 
LVL 19

Expert Comment

by:Manoj Patil
ID: 37810244
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
 

Author Comment

by:Webboy2008
ID: 37813393
My question is to show up agencyid 5, even the transaction table does not have agencyid 5 there.
0
 
LVL 42

Accepted Solution

by:
dqmq earned 500 total points
ID: 37814030
The SQL I've given you returns exactly those results.  Please try it.  If the results are not satisfactory, then please explain.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37814550
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
 
LVL 42

Expert Comment

by:dqmq
ID: 37814672
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37814779
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

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now