CODEWITHSUNDEEP
mysqlsql
systemdebt
systemdebt

Reputation: 4941

Get number of rows returned by subquery along with the columns returned by subquery

I find it really annoying to be not able to get the number of rows without having to use group by. I just need to get the "Total count" that my subquery returned.

Here is what my subquery looks like:

select sales_flat_order.increment_id, sales_flat_order.created_at, sales_flat_order.status, dispatch.dispatch_date, 
DATEDIFF(TO_DATE(dispatch.dispatch_date), TO_DATE(sales_flat_order.created_at)) as delay 
FROM 
magentodb.sales_flat_order 
LEFT OUTER JOIN erpdb.dispatch 
ON 
sales_flat_order.increment_id == dispatch.order_num 
where
TO_DATE(created_at) >= DATE_SUB(current_date(),6)
AND 
TO_DATE(created_at) <= DATE_SUB(current_date(), 3)
AND
sales_flat_order.status NOT IN ('canceled', 'exchange', 'rto', 'pending_auth', 'pending_payment' ,'partial_refund','refund', 'refund_cash', 'partial_refund_cash', 'holded')
)
AS TempFiltered

Now, I add 1 extra WHERE clause in my outer query so that it returned "lesser" number of rows, let's call this column y .

I then require to take percentage of x to y(i.e number of rows returned by outer query to subquery)

I do not wan to repeat my subquery only to get count of the rows. HOw do I get it?

This is what I have so far: But ofcourse it is wrong. I can not get count of all my rows without having to exclude select columns or using them in group by. HOw do I resolve this?

SELECT tempfiltered.delay, count(*) as countOfOrders,(100*count(*))/tempfiltered.Total) over () as percentage
FROM
(
select count(*) as Total, sales_flat_order.increment_id, sales_flat_order.created_at, sales_flat_order.status, dispatch.dispatch_date, 
DATEDIFF(TO_DATE(dispatch.dispatch_date), TO_DATE(sales_flat_order.created_at)) as delay 
FROM 
magentodb.sales_flat_order 
LEFT OUTER JOIN erpdb.dispatch 
ON 
sales_flat_order.increment_id == dispatch.order_num 
where
TO_DATE(created_at) >= DATE_SUB(current_date(),6)
AND 
TO_DATE(created_at) <= DATE_SUB(current_date(), 3)
AND
sales_flat_order.status NOT IN ('canceled', 'exchange', 'rto', 'pending_auth', 'pending_payment' ,'partial_refund','refund', 'refund_cash', 'partial_refund_cash', 'holded')
)
AS TempFiltered
Where 
 DATEDIFF(TO_DATE(TempFiltered.dispatch_date), TO_DATE(TempFiltered.created_at)) > 1     
GROUP BY tempfiltered.delay
ORDER BY tempfiltered.delay

Upvotes: 0

Views: 62

Answers (1)

John Bingham
John Bingham

Reputation: 2006

You could change the subquery into a SELECT INTO query, and put the data in a temporary table, and use that in the main query, and separately just select count(*) of that temporary table. That should pretty much satisfy your requirement.

Upvotes: 1

Related Questions