CODEWITHSUNDEEP
sqlsql-server-2005
user1103342
user1103342

Reputation: 113

use group by in subquery

I hava following sql query

SELECT animal_code, ISNULL(SUM(calve_milk + morning + evening), 0) / 30 AS [Sep 2011],
                          (SELECT     ISNULL(SUM(calve_milk + morning + evening), 0) / 31 AS Expr1
                            FROM          dbo.Status
                            WHERE      (m_date BETWEEN '10/1/2011' AND '10/31/2011')) AS [Oct 2011]
FROM         dbo.Status AS Status_1
WHERE     (m_date BETWEEN '9/1/2011' AND '9/30/2011')
GROUP BY animal_code
ORDER BY animal_code

Through this query I have calculated a sum between a date range and named it as [Sep 2011] and now i have to calculate the [oct 2011] sum grouping it by animal code but i think the fixed column is only possible in subquery but in subquery i am unable to group the sum by animal code please any one help me out to get that sum group by animal_code in subquery

Update

select a.animal_code,a.Sep2011,b.Oct2011 from 
(SELECT DISTINCT   Images.animal_code, 
REPLACE(REPLACE(ROUND(ISNULL(SUM(Status.calve_milk + Status.morning + Status.evening), 0) / 30, 0), '.', ''), '0', '') Sep2011
FROM Images,Status,animal_Status
where Images.animal_code=Status.animal_code
and     Images.status_id=animal_status.status_id
and status.m_date between '9/1/2011' and '9/30/2011'
and animal_status.status_id=8
group by animal_code) a,
(SELECT DISTINCT   Images.animal_code, 
REPLACE(REPLACE(ROUND(ISNULL(SUM(Status.calve_milk + Status.morning + Status.evening), 0) / 31, 0), '.', ''), '0', '') Oct2011
FROM Images,Status,animal_Status
where Images.animal_code=Status.animal_code
and     Images.status_id=animal_status.status_id
and status.m_date between '10/1/2011' and '10/31/2011'
and animal_status.status_id=8
group by animal_code) b
where a.animal_code=b.animal_code

Upvotes: 1

Views: 483

Answers (2)

Amen Ayach
Amen Ayach

Reputation: 4348

I am afread that you haven't seen my last comment

Ok i simulate your table, i make this smart query (i guess :) ):

SELECT  septable.code, septable.sep, octtable.oct
FROM    (SELECT  code, ISNULL(SUM(mor + milk + ev), 0) / 30 AS sep
             FROM    status
             WHERE  (m_date BETWEEN '09/01/2011' AND '09/30/2011')
             GROUP BY code) AS septable INNER JOIN
                (SELECT  code, ISNULL(SUM(mor + milk + ev), 0) / 31 AS oct
                FROM     status AS status_1
                WHERE  (m_date BETWEEN '10/01/2011' AND '10/30/2011')
                GROUP BY code) AS octtable ON octtable.code = septable.code

Upvotes: 1

t-clausen.dk
t-clausen.dk

Reputation: 44326

You can't expand your query with additional columns to include new months without getting garbage. Do that in a spread sheet or elsewhere.

Try this as your query. It does not have the exact format as it shows the results as rows rather than columns, but it will work for all months and future months. I replaced the 30 with the exact number of days for the month, also it shows with 2 digits instead of a result that is always rounded down. i chose to show you 2 different ways of displaying the month and year in the result

SELECT animal_code, SUM(isnull(calve_milk,0) + isnull(morning,0) + isnull(evening,0)) / 
-- this replaces your 30
cast(datediff(day, dateadd(month, datediff(month, 0, m_date), 0), dateadd(month, datediff(month, 0, m_date) + 1, 0)) as decimal(9,2))
AS average_milk_per_day,
--this will give you a varcharcolumn showing month and year
stuff(convert(varchar(11), dateadd(month,datediff(month, 0, m_date), 0), 100)
, 4,3,'') monthyear,
--this will give you the month and year as a datetime
dateadd(month, datediff(month, 0, m_date),0) monthyear_as_date
FROM dbo.Status AS Status_1 
WHERE m_date >= '2011-01-09'
GROUP BY animal_code,
datediff(month, 0, m_date)
ORDER BY datediff(month, 0, m_date), animal_code

Result for current month will be lower until all data has been registrered. If you only need it calculated for registrered days rather than whole months, let me know and i will adjust the query again.

Upvotes: 0

Related Questions