question

MkontwanaGcobaniEXTERNVWSAfrica-9452 avatar image
0 Votes"
MkontwanaGcobaniEXTERNVWSAfrica-9452 asked MelissaMa-msft answered

How to use Add function in sql server?

Hi Team

I need some help, i don't have a record the filter the total of Station Description. What i want to achieve, if there is a way in sql server query. Where i can use Add function that will give me. Number of total Station Description per Date Time Passed column. Similar to Excel when we use SUM(..........). But the result when this is used i must get Transaction Code(Station Description) per total each and DateTime Passed be shown on my results. Let me share my logic its working but i want to implement if possible to this query.

select
[Station Description]
[Transaction Code],
[DateTime Passed]
from [Tracking_Server_DB].[dbo].TS_Station as stn
inner join [Tracking_Server_DB].[dbo].[Checkpoint Movement] as mv
on stn.[Transaction Code] = mv.[Transaction Code]
order by [DateTime Passed] desc


sql-server-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @MkontwanaGcobaniEXTERNVWSAfrica-9452,

Could you please validate the answer and provide any update?

Best regards,
Melissa

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @MkontwanaGcobaniEXTERNVWSAfrica-9452,

As mentioned by Erland, since you have [Transaction Code] columns in both tables, you have to identity which table it comes from.

Please refer below updated ones.

  select
  count([Station Description]) total,
  stn.[Transaction Code],
  [DateTime Passed]
  from [Tracking_Server_DB].[dbo].TS_Station as stn
  inner join [Tracking_Server_DB].[dbo].[Checkpoint Movement] as mv
  on stn.[Transaction Code] = mv.[Transaction Code]
  group by stn.[Transaction Code],[DateTime Passed]
  order by [DateTime Passed] desc

OR

  select distinct
  count([Station Description]) over (partition by [DateTime Passed]) total,
  stn.[Transaction Code],
  [DateTime Passed]
  from [Tracking_Server_DB].[dbo].TS_Station as stn
  inner join [Tracking_Server_DB].[dbo].[Checkpoint Movement] as mv
  on stn.[Transaction Code] = mv.[Transaction Code]
  order by [DateTime Passed] desc

If above is still not working, please provide your CREATE TABLE statements for your tables together with INSERT statements with sample data, and the expected result of the sample. Then we could proceed to help you to enhance this query.

Thank you for understanding!

Best regards,
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered ErlandSommarskog commented

Hi @MkontwanaGcobaniEXTERNVWSAfrica-9452,

Welcome to Microsoft Q&A!

For this type of problem we recommend that you post CREATE TABLE statements for your tables together with INSERT statements with sample data, enough to illustrate all angles of the problem. We also need to see the expected result of the sample.

Say the data type of [Station Description] column is like varchar, you could refer below and check whether it is helpful.

 select
 count([Station Description]) total,
 [Transaction Code],
 [DateTime Passed]
 from [Tracking_Server_DB].[dbo].TS_Station as stn
 inner join [Tracking_Server_DB].[dbo].[Checkpoint Movement] as mv
 on stn.[Transaction Code] = mv.[Transaction Code]
 group by [Transaction Code],[DateTime Passed]
 order by [DateTime Passed] desc

OR

 select distinct
 count([Station Description]) over (partition by [DateTime Passed]) total,
 [Transaction Code],
 [DateTime Passed]
 from [Tracking_Server_DB].[dbo].TS_Station as stn
 inner join [Tracking_Server_DB].[dbo].[Checkpoint Movement] as mv
 on stn.[Transaction Code] = mv.[Transaction Code]
 order by [DateTime Passed] desc

If the data type of [Station Description] column is int or decimal, you could replace count with sum in above query.


Best regards,
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@MelissaMa-msft i am getting an error on one record 'Transaction Code' i do have this record on the table. Unambigious column name. How do i fix this error

0 Votes 0 ·
ErlandSommarskog avatar image ErlandSommarskog MkontwanaGcobaniEXTERNVWSAfrica-9452 ·

In the SELECT list you have [Transaction Code] only, but I can tell from line 7 above that this column name appears in two tables. SQL Server does not know which column to use, so you need to add a prefix, that is stn. or mv. (And since the join condition says that they should be the same, it does not matter which one you pick.)

0 Votes 0 ·