How to use Add function in sql server?

Question

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

Answer 1

Hi @Mkontwana, Gcobani (EXTERN: VW S.Africa) ,

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.

Answer 2

Hi @Mkontwana, Gcobani (EXTERN: VW S.Africa) ,

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.