How do you create query with multiple totals by date

pts.
Tags:
SQL
I am trying to create a query that has totals of a particular hour type by date. This is what I came up with: select work_detail.wrkd_work_date, (select ((sum(wrkd_minutes))/60) from work_detail where work_detail.htype_id = 2) as totalot1, (select ((sum(wrkd_minutes))/60) from work_detail where work_detail.htype_id = 3) as totalot2, (select ((sum(wrkd_minutes))/60) from work_detail, work_summary where (work_detail.wrks_id = work_summary.wrks_id) and (work_summary.wrks_authorized = 'N')) as totalua from work_detail, hour_type where work_detail.htype_id = hour_type.htype_id group by work_detail.wrkd_work_date The output rolls everything up into one total for all dates. How can I get totals for each day? This is the output: WRKD_WORK TOTALOT1 TOTALOT2 TOTALUA --------- ---------- ---------- ---------- 06-MAY-02 161.183333 1724.61667 18948.65 14-JUL-04 161.183333 1724.61667 18948.65 15-JUL-04 161.183333 1724.61667 18948.65 16-JUL-04 161.183333 1724.61667 18948.65 17-JUL-04 161.183333 1724.61667 18948.65 18-JUL-04 161.183333 1724.61667 18948.65 19-JUL-04 161.183333 1724.61667 18948.65 20-JUL-04 161.183333 1724.61667 18948.65 21-JUL-04 161.183333 1724.61667 18948.65 22-JUL-04 161.183333 1724.61667 18948.65 23-JUL-04 161.183333 1724.61667 18948.65 Thanks for your help!

Answer Wiki

Thanks. We'll let you know when a new response is added.

I believe you need to sort by and use the date field and the type filed that you group by. try that.

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Trinaa
    select work_detail.wrkd_work_date , sum( case when work_detail.htype_id = 2 then wrkd_minutes end ) /60 as totalot1 , sum( case when work_detail.htype_id = 3 then wrkd_minutes end ) /60 as totalot2 ,( select sum(wrkd_minutes) from work_summary where wrks_id = work_detail.wrks_id and wrks_authorized = 'N' ) /60 as totalua from work_detail inner join hour_type on work_detail.htype_id = hour_type.htype_id group by work_detail.wrkd_work_date
    0 pointsBadges:
    report
  • BarryCummings
    You could also try this (works OK for SQL Server 2000). The inner select adds a couple of calculated columns to match the 2 cases you are interested in, then the outer select performs the summary: SELECT Detail.wrkd_work_date, SUM(Mins_2) AS totalot2, SUM(Mins_3) AS totalot3, SUM(wrkd_minutes) AS totalua FROM ( SELECT WD.wrkd_work_date, (CASE WD.htype_id WHEN 2 THEN WD.wrkd_minutes ELSE 0 END) AS Mins_2, (CASE WD.htype_id WHEN 3 THEN WD.wrkd_minutes ELSE 0 END) AS Mins_3, WD.wrkd_minutes FROM work_summary WS INNER JOIN work_detail WD ON WD.wrks_id = WS.wrks_id INNER JOIN hour_type HT ON HT.htype_id = WD.htype_id WHERE WS.wrks_authorized = 'N' ) AS Detail GROUP BY wrkd_work_date
    0 pointsBadges:
    report
  • carlosdl
    The problem with your query is that the totals sub-queries calculate the sum for the entire table not for the actual row. Try using decode instead subqueries. For the totalua a subquery is needed because the other conditions, but this total must be calculated only for the actual row. this is the idea: select wd.wrkd_work_date, sum(decode(wd.htype_id,2,wrkd_minutes/60,0)) total1, sum(decode(wd.htype_id,3,wrkd_minutes/60,0)) total2, (select ((sum(wrkd_minutes))/60) from work_detail wd1, work_summary ws where (wd1.wrks_id = ws.wrks_id) and wd1.wrks_id = wd.wrks_id and (ws.wrks_authorized = 'N')) as totalua from work_detail wd, hour_type ht where wd.htype_id = ht.htype_id group by wd.wrkd_work_date
    69,160 pointsBadges:
    report
  • carlosdl
    The problem with your query is that the totals sub-queries calculate the sum for the entire table not for the actual row. Try using decode instead sub-queries. For the totalua a subquery is needed because of the other conditions, but this total must be calculated only for the actual row. this is the idea: select wd.wrkd_work_date, sum(decode(wd.htype_id,2,wrkd_minutes/60,0)) total1, sum(decode(wd.htype_id,3,wrkd_minutes/60,0)) total2, (select ((sum(wrkd_minutes))/60) from work_detail wd1, work_summary ws where (wd1.wrks_id = ws.wrks_id) and wd1.wrks_id = wd.wrks_id and (ws.wrks_authorized = 'N')) as totalua from work_detail wd, hour_type ht where wd.htype_id = ht.htype_id group by wd.wrkd_work_date
    69,160 pointsBadges:
    report
  • spintreebob
    Try nesting each sub-query in the FROM clause SELECT A.Result, B.Result, C.Result FROM (SELECT xxxx RESULT FROM xxxx WHERE) A ,(SELECT ) B ,(SELECT ) C WHERE . . . . . Of course, this places all results on the same output line. Creating a mixed inner query, outer query can format them as a table. Tools like QMF can do a great job at the formatting.
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following