How to use Add function in sql server?

2021-08-27T02:31:55.237+00:00

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,060 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,575 questions
{count} votes

Accepted answer
  1. MelissaMa-MSFT 24,176 Reputation points
    2021-08-31T01:10:48.763+00:00

    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.

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. MelissaMa-MSFT 24,176 Reputation points
    2021-08-27T05:43:03.727+00:00

    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.