Hardik Trivedi Hardik Trivedi - 2 months ago 17
SQL Question

How to perform sum on sum produced by group by

I am having Issue here guys.

SELECT
SUM(amount) AS t_rev
FROM
`ready_to_ship_detail`
WHERE
`job_id` = 18848
GROUP BY
invoice_number


Result:

| t_rev |
---------
| 250 |
| 100 |


This is because there are 2 different invoice numbers. But
I want SUM
of
250 and 100 = 350


Help me build a query which give me the sum of both these records or multiple/more than 2 if any.

This is sub-query of a large query so I am getting multiple rows in a sub-query error in MySQL.

So I want to have a query which returns me sum of such multiple values.

Answer
SET @t_rev := 0.0;
SET @t_margin := 0.0;
SET @j_hrs := 0.0;
SET @t_exp := 0.0;
SELECT q.quote_id, j.job_number, c.company_name, j.create_date, j.direct_labour_rate, j.overhead_labour_rate,
    @j_hrs := (SELECT SUM(TIME_TO_SEC(IF((end_date<>'00:00:00' AND NOT ISNULL(end_date)),TIMEDIFF(CONCAT(end_date ,' ', end_time),CONCAT(create_date ,' ', start_time)),TIMEDIFF(CONCAT(create_date ,' ', end_time),CONCAT(create_date ,' ', start_time))))/3600) AS job_time FROM production_master WHERE Job_number=j.job_number) AS j_time,
    @t_exp := (IFNULL((SELECT SUM(amount) FROM job_expenses WHERE job_id=j.job_number),0.0)) AS t_exp,
    @t_rev := (SELECT amount FROM ready_to_ship_detail WHERE job_id=j.job_number GROUP BY invoice_number HAVING invoice_number<>'') AS t_rev,
    @t_margin := (@t_rev/(((j.direct_labour_rate+j.overhead_labour_rate) * (@j_hrs)) + (@t_exp))) AS margin
    FROM quote_master q
    LEFT JOIN create_job_master j ON j.quote_id=q.quote_id
    LEFT JOIN company_master c ON q.company_id=c.company_id
    WHERE q.create_date BETWEEN '2016-04-01' AND '2016-04-30'
    ORDER BY c.company_name ASC, q.quote_id DESC

In this query there is the sub-query

@t_rev := (SELECT amount FROM ready_to_ship_detail WHERE job_id=j.job_number GROUP BY invoice_number HAVING invoice_number<>'') AS t_rev,

This is causing the trouble as there are multiple rows are given as result to the main query.

I have a logic but how to make it possible? So I want only

| t_rev |
---------
|  350  |

as an answer by MUST using GROUP BY invoice_number

Hope this clear my intention a bit more.

Comments