Hi All and Vijay, :-)

I have always used MS Access for my database, but now I am using SQL 2000. I know in Access, it has a group by function, and also the "First" and "Last" function. These two functions grab either the first or the last record if your queried records contain duplicates. However, in SQL, there is no such thing as "First" and "Last" function.

So, in Access let say I want to total on a column(named Sales_total), I can do a group by, and sum on the Sales_total. And if I want to include additional columns, and NOT do any calculation to them, just for the sake of it, I can use either "First" or "Last" in those columns. Now how can i do this using SQL 2000? I notice the ONLY thing i can do in order to include those extra columns is to do a group_by like this:

select format_nm, status, emp_id, dedplan_cd, plan_desc, sum(ee_amt) As EE_Amt, sum(admin_amt) as
Admin_Amt, sum(cnty_amt) as Cnty_Amt, sum(goal_amt) as Goal_Amt, sum(goal_totl) as Goal_Total
from dedparms
Group By emp_id, format_nm, status, dedplan_cd, plan_desc
GO

My concern is, since group by function is like one of those "sub-group," I am afraid the sum of my "Amt" columns will be wrong. Any idea?

Thank you.

Leon

Can it help you .. ?

   SELECT * , (SELECT Sum(cnty_amt) From Dedparms Group By emp_id) As SumCnty_Amt, [other sum] From Dedparms Ordery By Emp_Id ......

   Stay Beautiful,

Abdul Salam

Hi Leon,

Sorry, I somehow missed this for long time.

The equivalent of MSAccess's FIRST / LAST can be achieved in SQL 2000 using TOP Keyword.

-- Gives the lowest value, ASC(ascending sort order) by Default
-- Gives the highest value
Maybe you can post you access query here to have a look at it, as I am not sure on what you are trying to achieve.

Cheers!

_________________________
- Vijay G
Strive for Perfection

Hi Vijay,

Glad to hear from you. No apology necessary. Let me explain what I want to achieve first, and then I will give u the query string. Ok, I have two separate tables(table1 and table2). Both of these tables contain duplicates of employee information. Therefore, as you know, if you query in SQL or any database with a "SELECT" stmt from both tables, you get what they call a 'cartesian product.' What I want to do is get a result where all the duplcates are combined into one row/record. In MS Access, I can do something like this:

select format_nm, First(status), emp_id, First(dedplan_cd), First(plan_desc), sum(ee_amt) As EE_Amt, sum(admin_amt) as
Admin_Amt, sum(cnty_amt) as Cnty_Amt, sum(goal_amt) as Goal_Amt, sum(goal_totl) as Goal_Total
from dedparms
Group By emp_id, format_nm

Notice the "First" keyword I use for First(status), First(dedplan_cd), First(plan_desc)
This way I can include the status, dedplan_cd, plan_desc columns in my resulted table. Also note that I don't have to include these columns in my "Group By."

However, if I wanted to do this in SQL and want to include these 3 columns, I have to list them at the 'Group By' statement. So....a SQL 2000 stmt. would look something like this:
....Group By emp_id, format_nm, status, dedplan_cd, plan_desc

If I were to do this, I am concern that it causes sub-group. This in turn, will create incorrect calculation. What I am trying to say is, since I am having so many columns in Group BY, I am afraid it will mess up something. Make sense?

Thank you.

Leon

Hi Leon,

As you say, adding those columns into GROUP BY, it would be a mess. But still without getting a clear picture of relation between those 2 tables, I cannot suggest you how it would work.

Also I don't understand why it has to give duplicate values when you have them in different tables and a join in place? Does that need to have a re-look into table design? I am not sure.

I assume that if you don't apply FIRST() on dedplan_cd and plan_desc, you should be getting duplicate values. The same can be achieved using DISTINCT keyword in SQL. But still I am not sure how that would help/affect in your case, without knowing what the table1 and table2 contain and how they are related.

Also, I don't see a join in place in the query that you used in ACCESS.

Cheers!

_________________________
- Vijay G
Strive for Perfection

Hi Vijay,

The relation between the two tables is by SSN. I am working with my company's data. Just for an example sake. Table1 has 3 records(Health, Dental, Vision) per person. Each record has different amounts that we SUPPOSE to charge the person.

Now table2 have the SAME EXACT set up BUT the amounts from the table is what we ACTUALLY charged the person.

Now what I want to do is create a result table(from these two tables) where I have ONE record per individual, AND with the amounts right next to each other. This way I can run comparison reports. Make sense? So....the result table i would like it to look like is this:

SSN|T1.Dentl|T1.Health|T1.Vision|T2.Dentl|T2.Healt h|T2.Vision

To get this, the only way I can think of is use the 'Group By' function.

However, the problem when I do a select stmt to get the result table, I get like 9 records per person(due to Cartesian Product), right? Right! And when the 'Group By' function and Sum function comes in so I can get ONE record per person, it will add all 9 rows(of the same SSN) together, causing the amounts to be incorrect. Because I don't want to have the T1.Health, T1.Dental, T1.Vision, T2.Health, T2.Dental, T2.Vision added up 9 times. So my question is, how can I do it to get the result table I mentioned above?

Thank you.

Leon

Hi,

If you figure this out. Please let me know!
We're having the same problem

thx

Hi Zmascaros,

Sorry for the late reply. I figured out the way to do it. Well first, since the 3 amount in each table are different by health, dental, and vision, we can separate them. So what I did was create 3 new columns in each table, then write a stored procedure to "update" each of the 3 new column accordinly. As you can see, the purpose of this is to do a "group by" after the updates. Again, make this group by a stored procedure. After the group by of each table, you should have one record per individual, correct? Correct!

Create view dedparmH as
select format_nm, emp_id, status, dedplan_cd, plan_desc, left(dedtype_cd,2) as type, agency, sum(over_amt) as Over_Amt, sum(ee_amt) As EE_Amt, sum(admin_amt) as Admin_Amt, sum(cnty_amt) as Cnty_Amt, sum(goal_amt) as Goal_Amt, sum(goal_totl) as Goal_Total
from dedparms
Group By format_nm, emp_id, status, dedplan_cd, plan_desc, left(dedtype_cd,2), agency


Now, what you do is do a "select" stmt to combine the 2 different table based on the person's unique identifier. This is what I used to do my combine.

Hope this helps! This works for me. So test it out first for your situation. If you have any question, I'll be glad to try to help.


Create view Parms_n_Detail as
SELECT dedparmh.emp_id, dedparmh.format_nm, dedparmh.status, dedparmh.agency, dedparmh.dedplan_cd, dedparmh.plan_desc, dedparmH.type, dedparmH.cnty_amt as County_Amt, sum(dedetailH.cnty_actl) as County_Actl, dedparmH.admin_amt as Admin_Amt, sum(dedetailH.admin_actl) as Admin_Actl, dedparmH.ee_amt as Employee_Amt,
sum(dedetailH.ee_actl) as Employee_Actl
from dedparmh, dedetailH
where dedparmH.emp_id=dedetailH.emp_id and dedparmh.type=dedetailh.type
group by dedparmh.emp_id, dedparmh.format_nm,dedparmh.status, dedparmh.agency, dedparmh.dedplan_cd, dedparmh.plan_desc, dedparmh.type, dedparmh.cnty_amt, dedparmh.ee_amt, dedparmh.admin_amt