mysql sum totals without using with rollup

Ask about general coding issues or problems here.

Moderators: egami, macek, gesf

Post Reply
garybrett17
New php-forum User
New php-forum User
Posts: 1
Joined: Wed Jan 17, 2018 7:28 am

Wed Jan 17, 2018 7:31 am

I am using a phpgrid to display query data, then filtering through form select on page. This works fine until I add with rollup to the query to sum columns and I wondered if there is any other method of summing these columns (12 of them which are months)?

This doesn't work telling me Couldn't execute query. Unknown column 'i.signedupdate' in 'where clause

Code: Select all

SELECT * FROM
  (SELECT IFNULL(c.Adviser, 'GRAND TOTAL') AS Adviser,
  Sum(Month(i.SignedUpDate) = 1) As Jan,
  Sum(Month(i.SignedUpDate) = 2) As Feb,
  Sum(Month(i.SignedUpDate) = 3) As Mar,
  Sum(Month(i.SignedUpDate) = 4) As Apr,
  Sum(Month(i.SignedUpDate) = 5) As May,
  Sum(Month(i.SignedUpDate) = 6) As Jun,
  Sum(Month(i.SignedUpDate) = 7) As Jul,
  Sum(Month(i.SignedUpDate) = 8) As Aug,
  Sum(Month(i.SignedUpDate) = 9) As Sept,
  Sum(Month(i.SignedUpDate) = 10) As Oct,
  Sum(Month(i.SignedUpDate) = 11) As Nov,
  Sum(Month(i.SignedUpDate) = 12) As Dece,
  i.id,
  Count(i.id) As Total
    From
  tbl_lead i Inner Join
  tbl_clients c On i.client_id = c.client_id
Group By
    c.Adviser with rollup) As t
This works, I can select years ok but no column summary

Code: Select all

SELECT * FROM
  c.Adviser AS Adviser,
  Sum(Month(i.SignedUpDate) = 1) As Jan,
  Sum(Month(i.SignedUpDate) = 2) As Feb,
  Sum(Month(i.SignedUpDate) = 3) As Mar,
  Sum(Month(i.SignedUpDate) = 4) As Apr,
  Sum(Month(i.SignedUpDate) = 5) As May,
  Sum(Month(i.SignedUpDate) = 6) As Jun,
  Sum(Month(i.SignedUpDate) = 7) As Jul,
  Sum(Month(i.SignedUpDate) = 8) As Aug,
  Sum(Month(i.SignedUpDate) = 9) As Sept,
  Sum(Month(i.SignedUpDate) = 10) As Oct,
  Sum(Month(i.SignedUpDate) = 11) As Nov,
  Sum(Month(i.SignedUpDate) = 12) As Dece,
  i.id,
  Count(i.id) As Total
    From
  tbl_lead i Inner Join
  tbl_clients c On i.client_id = c.client_id
Group By
    c.Adviser
If I simply remove the subquery and leave 'with rollup' it alerts me of incorrect use of order by & with rollup?

Any ideas much appreciated, just need to sum all those column

User avatar
freshnet
php-forum Active User
php-forum Active User
Posts: 363
Joined: Tue Feb 22, 2011 8:19 am
Location: Canada

Sat Jan 20, 2018 9:02 am

A couple of quick thoughts

1. That error references the WHERE clause, however your posted query doesn't have a WHERE clause. So either you're looking at the wrong error, or you've not posted the actual query you're using
2. The first query works, however you should remove the i.id from the SELECT as you're not grouping by it.
Step one with troubleshooting queries is always to echo back the full query and run it to see if you get an error.
If you are asking for help with code, please provide the code, the expected result and the actual result, or you will likely not get any replies.

Post Reply