CODEWITHSUNDEEP
sqlsql-server-2005
Learning Singh
Learning Singh

Reputation: 45

SUM on a Column Group

i have three tables with structure something like

tasktime - starttime,endtime,packagedetailid,packageid
packagedetailid - packageid,productid, etc
productime - productid and searchesperday

and the query i am using on these is as follows

SELECT CONVERT(varchar, t.StartTime, 111) AS 'Date'
, SUM(DATEDIFF(second, t.StartTime, t.EndTime)) / 60 AS DailyMinutes
, COUNT(DISTINCT p.PackageDetailID) AS OrderCount
, LEFT(CAST(SUM(DATEDIFF(minute, t.StartTime, t.EndTime))
     / (COUNT(DISTINCT p.PackageDetailID) + 0.0000001) AS varchar), 4) AS PerOrder
, LEFT(CAST((COUNT(DISTINCT p.PackageDetailID) * 100)
     / (e.SearchesPerDay + 0.00000001) AS varchar), 4) AS WeightedOrderCount
FROM  ProductTime AS e INNER JOIN dbo.PackageDetails AS p
 WITH (NOLOCK) ON e.ProductID = p.ProductID INNER JOIN 
TaskTime AS t WITH (NOLOCK) ON p.PackageDetailID = t.PackageDetailId
WHERE(t.EndTime IS NOT NULL) 
AND (DATEDIFF(hour, t.StartTime, t.EndTime) < 1) 
AND (DATEDIFF(second, t.StartTime, t.EndTime) > 0) 
AND (t.UserId = '12345') 
AND (t.StartTime BETWEEN '1/1/2010'
AND '9/13/2010')
GROUP BY CONVERT(varchar, t.StartTime, 111), e.SearchesPerDay
ORDER BY CONVERT(varchar, t.StartTime, 111)

The OUTPUT is: 

**date         dailyminutes  ordercount  perorder  weightordercount**

2010/01/04     104                26    4.03    43.30
2010/01/04     10                  1    9.99     1.24
2010/01/04     19                  8    2.37     8.88
2010/01/04     22                 11    1.99    10.90
2010/01/04     18                  5    3.59     2.77
2010/01/05     49                 17    2.99    28.30
2010/01/05     31                  5    6.39     5.55
2010/01/05     26                  6    4.33     5.99
2010/01/05      8                  4    1.99     3.33

But i want to SUM up the "Weightordercount" against the date so that the output is

**date            dailyminutes  ordercount  perorder  weightordercount**

2010/01/04         173           51     21.97       67.09
2010/01/05         114           32     15.70       43.17

i am not a sql expert and need your help if this can be achieved by a Single SQL command or through a Strored Procedure

Thanks in advance

Upvotes: 1

Views: 194

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280431

(1) Don't use varchar without length - but why are you converting to a varchar anyway?

(2) Why are you grouping by searches per day? I'm guessing this needs to be a SUM to be meaningful, here is how I would do it:

;WITH x AS
(
    SELECT 
        [Date]         = DATEDIFF(DAY, '19000101', t.StartTime),
        DailyMinutes   = SUM(DATEDIFF(SECOND, t.StartTime, t.EndTime)),
        OrderCount     = COUNT(DISTINCT p.PackageDetailID),
        SearchesPerDay = SUM(e.SearchesPerDay)
    FROM 
        dbo.ProductTime AS e
    INNER JOIN 
        dbo.PackageDetails AS p
        ON e.ProductID = p.ProductID
    INNER JOIN 
        dbo.TaskTime AS t
        ON p.PackageDetailID = t.PackageDetailID
    WHERE 
        t.EndTime IS NOT NULL
        AND (DATEDIFF(SECOND, t.StartTime, t.EndTime) BETWEEN 1 AND 3599) 
        AND (t.UserId = '12345') 
        AND (t.StartTime >= '20100101' AND t.StartTime <= '20100913')
    GROUP BY 
        DATEDIFF(DAY, '19000101', t.StartTime)
)
SELECT 
    [Date] = DATEADD(DAY, [Date], '19000101'),
    DailyMinutes,
    OrderCount,
    PerOrder = CONVERT(DECIMAL(10,2), (DailyMinutes * 1.0 / OrderCount)),
    WeightedOrderCount = CONVERT(DECIMAL(10,2), (100.0 * OrderCount / SearchesPerDay))
FROM
    x
ORDER BY
    [Date];

A couple of other enhancements:

(a) don't use BETWEEN for date/time, use >= and < (I left the end as <= but it's likely you either meant < 9/14 or < 9/13).

(b) don't use regional formats for dates - '09/13/2010' is not a valid date depending on language, dateformat or regional settings. 'YYYYMMDD' is the safest format to use in SQL Server for date only.

(c) try to perform calculations as few times as possible - I moved repeated calculations into a CTE so that reference in other calculations can be much simpler. You don't need a CTE, you can also use a subquery. This is also the reason I combined the two checks against the delta between StartTime and EndTime.

(d) your method for getting decimals was rather crude - adding decimal places, converting to a string, taking the left...

Upvotes: 1

Derek
Derek

Reputation: 23268

Your code is horribly formatted in your post, so I'm going to give you a general answer:

You need to use a GROUP BY clause and then SUM each of your aggregates.

So something like this:

select date, sum(dailyminutes), sum(ordercount), sum(perorder), sum(weightordercount)
from (yourentirequery) a
group by date
order by date asc

Upvotes: 3

Related Questions