0

I have a complex query that consists of JOINs, GROUP BYs and complex WHEREs. Because there are many rows, the output is limited to 100 rows per page (paginated). I can NOT remove the limit and limit in app code.

However, I need the count and sum of a column for all entries that match the query. For the count I can use SQL_CALC_FOUND_ROWS, for the sum I see no way around a second query for now - am I wrong?

Since SqlFiddle won't work, here is a simplified example: (I need a total sum of all p.amount's or a SUM of the p_sum's.)

CREATE TABLE users
    (`customer_id` int, `company` varchar(255), `belongstoclient` int)
;
CREATE TABLE something
    (`id` int, `customer_id` int, `amount` int, `type` int)
;

INSERT INTO users
    (`customer_id`, `company`, `belongstoclient`)
VALUES
    (1, 'Company A', 1),
    (2, 'Company B', 1),
    (3, 'Company C', 2),
    (4, 'Company D', 1)
;

INSERT INTO something
    (`id`, `customer_id`, `amount`, `type`)
VALUES
    (1, 1, 17, 2),
    (2, 2, 34, 3),
    (3, 2, 20, 2),
    (4, 4, 13, 2),
    (5, 3, 11, 2)
;

With the (simplified) query:

SELECT SQL_CALC_FOUND_ROWS 
    u.customer_id as `customer_id`,
    u.company as `company`,
    COUNT(p.id) as `p_count`,
    SUM(p.amount) as `p_sum`,
    p.type as `p_type`
FROM `users` u
LEFT JOIN `something` p 
ON p.customer_id=u.customer_id
WHERE u.belongstoclient=1 
GROUP BY p.type
ORDER BY u.customer_id
DESC LIMIT 0,100
Manuel Arwed Schmidt
  • 3,376
  • 2
  • 18
  • 28
  • 6
    you can be wrong or right but if you don't show us the query and the data table it will be a shot in the dark to help you. A sqlfiddle too would be the best – Lelio Faieta Sep 29 '15 at 08:22
  • you need to show how your table constructed – Dhiraj Wakchaure Sep 29 '15 at 08:45
  • Adjusted the description. – Manuel Arwed Schmidt Sep 29 '15 at 08:50
  • You can use COUNT(*) instead of SQL_CALC_FOUND_ROWS as it will be faster. See this - http://stackoverflow.com/questions/186588/which-is-fastest-select-sql-calc-found-rows-from-table-or-select-count. And I din;t get your second point, which data you want to sum. – Ranjana Sep 29 '15 at 09:08
  • I need a total sum of all p.amount's or a SUM of the p_sum's. Also, in my knowledge COUNT(*) won't work because of the LIMIT involved here. That's why we need the SQL_CALC_FOUND_ROWs. Please correct me, if I'm wrong. – Manuel Arwed Schmidt Sep 29 '15 at 09:10

0 Answers0