• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 348
  • Last Modified:

How to have subquery with aggregate?

Hi Experts,

We have a table with customer order history data. Given a specific year and a month, the custid is unique. The table is with fields like:
custid, year, month, ord_amt

Now I need to create a view for each customer id with ytd_amt and mtd_amt. The view will have three fields:
custid, ytd_amt, mtd_amt

Can you help?
0
Castlewood
Asked:
Castlewood
1 Solution
 
TempDBACommented:
What is ytd_amt and mtd_amt? Do you have any logic for creating them?
0
 
CastlewoodAuthor Commented:
ytd_amt is for the year-to-day order amount. it should be equal to
selelct ord_amt from tab1 where tab1.year=YEAR(getdate())

mtd_amt is for the month-to-day order amount. it should be equal to
select ord_amt from tab1 where tab1.year=YEAR(getdate()) and tab1.month=MONTH(getdate())
0
 
TempDBACommented:
With view its little difficult. How about a stored procedure?
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
CastlewoodAuthor Commented:
Sorry, but I forgot to aggregate:

ytd_amt is for the year-to-day order amount. it should be equal to
selelct custid, SUM(ord_amt) from tab1 where tab1.year=YEAR(getdate()) GROUP BY custid

mtd_amt is for the month-to-day order amount. it should be equal to
select custid, SUM(ord_amt) from tab1 where tab1.year=YEAR(getdate()) and tab1.month=MONTH(getdate()) GROUP BY custid
0
 
CastlewoodAuthor Commented:
Cannot it be done by sub-query?
0
 
Vijaya Reddy Pinnapa ReddyCommented:
We can get the data invidually(monthly and yearwise data).But, both in same query is not possible.
It is aslo possible with SSRS
0
 
CastlewoodAuthor Commented:
Then, how to do it with a stored procedure?
0
 
TempDBACommented:
It can be, but performance won't be good. Anyway.

CREATE VIEW VIEWNAME
AS
BEGIN
      SELECT t1.custId, t1.ytd_amt, t2.mtd_amt
      (SELECT custId, sum(ord_amt) as ytd_amt
      FROM table1
      WHERE table1.year = YEAR(getdate())
      ORDER BY CustId) t1
      left outer join
      (SELECT custId, sum(ord_amt) as mtd_amt
      FROM table1
      WHERE table1.year = YEAR(getdate() and table1.month = MONTH(getdate())
      ORDER BY CustId) t2
      on t1.custId = t2.custId
END
0
 
TempDBACommented:
Try this as well. Sorry I don't have system with me right now. You need to verify the output.

CREATE VIEW VIEWNAME
AS
BEGIN
      SELECT t1.custId,
             ytd_amt = SUM(t1.ord_amt),
               mtd_amt = Case when t1.month = MONTH(getdate()) then SUM(t1.ord_amt) else 0 END            
      From table t1
      where t1.year = Year(getdate())
      Group by t1.custId
END
0
 
CastlewoodAuthor Commented:
Thank you for the prompt reply.
Since I'm not familiar with stored procedure can you tell me  how to proceed from here with your script in order to create the view?
thanks.
0
 
Scott PletcherSenior DBACommented:
CREATE VIEW dbo.viewname AS
SELECT
    custid,
    SUM(ord_amt) AS YTD_Amt,
    SUM(CASE WHEN t1.month = MONTH(GETDATE()) THEN ord_amt ELSE 0 END) AS MTD_Amt
FROM dbo.tablename tn
WHERE
    tn.year = YEAR(GETDATE())
GO


Are the year and month really stored as separate columns or is it a single data column?

If it's a single data column, you should not use any functions on the column.

WHERE
    tn.datetime_column >= 'yyyy0101' AND
    tn.datetime_column < 'yyyz0101' --where yyyz = yyyy + 1
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now