SQL

  • Hello All,

    When I run the query below it works fine, but when I un-comment the listed fields I get one record for each transaction during the date range. I just want the data summed up for each individual Merchant_Key and also include the commented out fields. What am doing wrong? Thanks in advance! 🙂

    SELECT DISTINCT(M.Merchant_Key), -- M.DBA_VC, M.Merchant_ID,

    SUM(Total_Amt_MN) AS TOTALAMT,

    COUNT(*) AS TOTALCOUNT,

    MAX(A.Date_Dt) AS 'Last Transaction Date'

    --CASE M.[Status]

    --WHEN '1' THEN 'Active'

    --WHEN '2' THEN 'Inactive' else '' end AS 'Status',

    -- A.Processor_ID

    FROM TrxDetail A (NOLOCK)

    LEFT OUTER JOIN TRX_Invoice_T I (NOLOCK)

    ON A.TRX_HD_Key = I.TRX_HD_Key

    JOIN MerchantInfo M (NOLOCK)

    ON M.Merchant_key = A.Merchant_Key

    JOIN dbo.Reseller_T R (NOLOCK)

    ON R.Reseller_Key = M.Reseller_Key

    WHERE (A.Payment_Type_ID IN ('XXX','XXX','XXX','XXX','XXX'))

    AND (ISNULL(A.Result_CH, '99999') = '0')

    AND (A.Settle_Date_DT >= '2014-01-01' AND A.Settle_DATE_DT < '2015-1-1')

    AND (A.Reseller_Key != 100)

    AND (A.Settle_Flag_CH = 1)

    AND M.Status IN (1,2)

    GROUP BY M.Merchant_Key --, M.DBA_VC, M.Merchant_ID, A.Date_DT, M.[Status], A.Processor_ID

    ORDER BY 1

  • A group by can only contain the grouped columns or aggregates. If you change the column in the group by, you roll up at a different granularity. Since there are many different values of those various fields which COULD be used, you have to tell it which one you want to use. Either by using an aggregate on those fields as well (something like max()) or by using a subquery.

    Executive Junior Cowboy Developer, Esq.[/url]

  • I would recommend not littering your database with that NOLOCK hint. This appears to be a financial application. Are you aware of the ramifications of that hint? Is accuracy not important? What about missing and/or duplicate rows?

    Here are a couple of links explaining how bad that hint really is.

    http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx

    http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx

    If you are deadset on using it, at least code it correctly. Not including the WITH keyword for query hints has been deprecated. The WITH keyword is going to be required in the future.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • JeeTee, thanks for the input. Can you provide a sample of how I could construct the sql using a sub-query using my query as a template?

    Sean Lange, I'm aware of the issue using the NOLOCK hint, but for this particular one time query it wasn't an issue. I will review the provided.

    I found that removing A.Date_Dt column which was used for 'Last Transaction' allowed my query to execute and sum the total_amt for each Merchant instead of producing one row for each transaction during the specified date range.

    Thanks for your help! It's appreciated!!

  • Id be happy to if you can provide me some sample data, and expected results.

    Executive Junior Cowboy Developer, Esq.[/url]

  • Even without your data, let me give you an example. Say I have a table full of end-of-day stock prices, as well as the sector in which that stock resides. I want to sum up the prices for all the days, and then also output the sector the company is in. If I understood your OP, this should bare a resemblance.

    Consider the following sample data:

    if object_id('tempdb.dbo.#dat') is not null drop table #dat

    create table #dat

    (

    Company varchar(10),

    TradeDate date,

    Sector varchar(30),

    Price decimal(9,5)

    primary key clustered (Company,TradeDate)

    )

    insert into #dat (Company, TradeDate, Sector, Price)

    select 'GOOGL', '2014-08-29', 'Computers', 3.14 union all

    select 'GOOGL', '2014-08-30', 'Computers', 4.14 union all

    select 'GOOGL', '2014-08-31', 'Technology', 5.14 union all

    select 'MSFT', '2014-07-05', 'Computers', 5.14 union all

    select 'MSFT', '2014-07-06', 'Computers', 5.14

    To get the sum by itself, you simply group by company, and then output all the aggregates you want.

    select

    Company,

    SumPrice = sum(Price),

    Ct = count(1)

    from #dat

    group by Company

    Works great so far, and I believe this is similar to the state you were in when you posted the query. But now we have a problem. We also want the sector of the company. We know that we can only include fields that are either part of the group by, or are aggregates themselves. So lets try including Sector in the group by condition

    select

    Company,

    SumPrice = sum(Price),

    Ct = count(1),

    Sector

    from #dat

    group by Company, Sector

    Uh oh, now we have a problem. we're getting more rows back than we wanted. Why? At two different points in time, the value for Sector was different. And since we've included that in the group by condition, we're going to get a new grouping every time the combination of Company and Sector differs.

    So what are your options? One is a little hacky, but works in some scenarios (probably NOT in this case, were it real, but it's worth pointing out). Lets take Sector OUT of the group by, and just select min(Sector) or max(Sector) in the projection list.

    select

    Company,

    SumPrice = sum(Price),

    Ct = count(1),

    Sector = min(Sector)

    from #dat

    group by Company

    Great, now we're back to one row per company, but it picked Computers as the sector for Google. Depending on your business logic, it might make sense to pick the sector associated with the most recent trading date. And now, unfortunately, you're out of quick and easy solutions to the problem. Now we're going to start having to use subqueries in places to get this data.

    There are more than one way to do this, but here's one approach. Get the grouping you want and put that in a derived table (aliased below as "d"). Then using a row_number, find out which record you want to represent the sector (aliased below as "s"), then join those two together

    select

    Company = d.Company,

    SumPrice = d.SumPrice,

    Ct = d.Ct,

    Sector = s.Sector

    from (select

    RID = row_number() over(partition by Company order by TradeDate desc),

    Company,

    Sector

    from #dat) s

    inner join (select

    Company,

    SumPrice = sum(Price),

    Ct = count(1)

    from #dat

    group by Company) d

    on s.Company = d.Company

    where s.RID = 1

    Executive Junior Cowboy Developer, Esq.[/url]

  • JeeTee,

    These are some awesome examples! I will take a look and change to the logic provided.

    Thanks!!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply