locked
how to get maximum and minimum year from the given sql without sum of profit,sales RRS feed

  • Question

  • select  max(yearno), min(yearno)

    yearno
    profit
    sales

    year(salesdate)

    month(salesdate)

    from tblsales

    where yearno in(2017,2018)


    polachan

    Wednesday, July 10, 2019 2:57 PM

All replies

  • Post DDL and script to insert sample data plus the output you expect.

    A Fan of SSIS, SSRS and SSAS

    Wednesday, July 10, 2019 3:11 PM
  • Hi polachan,

     

    Thanks for your question. Could  you please post a script with CREATE TABLE + INSERT statements with sample data and the expected result of that sample data? This clarifies what you are asking about.

     

    There're some issues I'm confused with:

    (1) what is yearno? Is it a given column or got by ''year(salesdate)''?

    (2) yearno is limited in 2017 and 2018, why do you still need a max and min year ?  (clearly max is 2018, min is 2017)

    (3) If you want to use max, you need to follow ''group by'' which can be show in your error message.

     

    Anyway, please offer us your desire outcome and sample data, so that we’ll get a right direction and make some test.

    When you post your issue, please refer POSTING TIPS - Code, Images, Hyperlinks, Details.

     

    Regards,

    Sabrina


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, July 11, 2019 2:53 AM
  • So you  have the below SELECT statement 

    SELECT yearno,profit,sales  from tblsales 

    And you want  to return MAX and MIN for yerno without specify profit,sales columns? Do you want  to get the aggregation GROUPed by sales or waht?

    SELECT max(yearno), min(yearno)

    FROM

    (

    SELECT yearno,profit,sales  from tblsales WHERE.......

    ) AS Der 


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, July 11, 2019 4:59 AM
    Answerer