Databases
StackOverflow2013
Postsdbo
POuse group by in subquery
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POuse group by in subquery
    primarykey
    data
    text
    <p>I hava following sql query</p> <pre><code>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 </code></pre> <p>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</p> <p><strong>Update</strong></p> <pre><code>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 </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USuser1103342
    1. USuser1103342
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POuse group by in subquery
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POuse group by in subquery
      1. USHossein
      1. VTFavorite
    1. COYou should read my answer and throw your own answer in the garbage collector, you should never expand your query with new columns new for new months.
      singulars
      1. POuse group by in subquery
      1. USt-clausen.dk
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload