January 13, 2015 at 4:26 pm
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
January 13, 2015 at 7:09 pm
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.
January 14, 2015 at 7:38 am
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/
January 14, 2015 at 8:56 am
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!!
January 14, 2015 at 9:02 am
January 14, 2015 at 9:21 am
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
January 14, 2015 at 9:31 am
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