as far as a I know this is more than what the Report Builder will do. But for cases like this, why not use a SQL report? This provides all features that SQL has. Maybe something like this:
SELECT `patient` AS `Patient`, COUNT(`name`) AS `Count` FROM `tabConsultation` GROUP BY `patient`;
Thanks Alot Dear for Your Guide
actually i got this error.
Traceback (most recent call last):
File “/home/frappe/frappe-bench/apps/frappe/frappe/app.py”, line 62, in application
response = frappe.handler.handle()
File “/home/frappe/frappe-bench/apps/frappe/frappe/handler.py”, line 22, in handle
data = execute_cmd(cmd)
File “/home/frappe/frappe-bench/apps/frappe/frappe/handler.py”, line 53, in execute_cmd
return frappe.call(method, **frappe.form_dict)
File “/home/frappe/frappe-bench/apps/frappe/frappe/init.py”, line 939, in call
return fn(*args, **newargs)
File “/home/frappe/frappe-bench/apps/frappe/frappe/desk/query_report.py”, line 90, in run
result = [list(t) for t in frappe.db.sql(report.query, filters)]
File “/home/frappe/frappe-bench/apps/frappe/frappe/database.py”, line 166, in sql
self._cursor.execute(query, values)
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/cursors.py”, line 165, in execute
result = self._query(query)
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/cursors.py”, line 321, in _query
conn.query(q)
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py”, line 860, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py”, line 1061, in _read_query_result
result.read()
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py”, line 1349, in read
first_packet = self.connection._read_packet()
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py”, line 1018, in _read_packet
packet.check_error()
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py”, line 384, in check_error
err.raise_mysql_exception(self._data)
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/err.py”, line 107, in raise_mysql_exception
raise errorclass(errno, errval)
ProgrammingError: (1064, u"You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘tabConsultationGROUP BYpatient`’ at line 1"
The error message looks like there are some ` missing (sorry, the ending ` of Count was missing in my post ). Make sure you have exactly this (note that ` and ’ are not the same in SQL)
SELECT `patient` AS `Patient`, COUNT(`name`) AS `Count`
FROM `tabConsultation`
GROUP BY `patient`;
To troubleshoot, you can also enter the MariaDB console using
$ cd /home/frappe/frappe-bench && bench mysql
Or, if you prefer UI tools, have a look into MySQL Workbench.
SELECT patientAS ID:Link/Patient:200 ,patient_nameAS Name,email,city_ AS city, COUNT(city_) AS Location, COUNT(patient) AS Visits
FROM tabPatient Appointment
GROUP BY city_ , patient
ORDER BY patient ASC;
can we add a total row where can sum the visit under visits and Locations under locations
i searched a bout SUM function but i found its sum fileds of colume that is already exist in the table
but the
COUNT colume that we got from the query its not a colume as i think so the SUM function cant deal with
am i right ?
even i attepted to make some thing like this
SUM(COUNT(patient))
or SUM(Count)
But it didnt work because it cant consider COUNT as a colume as i guess.
There are two options. Frappe has a built-in function “add total row”, which should do exactly this. However, sometimes it might not work. In that case, use something like this:
(SELECT product,
quantity,
something
FROM tablename)
UNION
(SELECT "all" AS product,
SUM(quantity) AS quantity,
SUM(something) AS something
FROM tablename)
so in your case
(SELECT `patient` AS `Patient:Link/Patient:200`, COUNT(`name`) AS `Visits`
FROM `tabConsultation`
GROUP BY `patient`)
UNION
(SELECT "all" AS `Patient:Link/Patient:200`, SUM(`Visits`) AS `Visits`
FROM `tabConsultation`);
SELECT patient AS Patient:Link/Patient:200, COUNT(name) AS Count
FROM tabConsultation
GROUP BY patient
UNION
SELECT “all” AS Patient:Link/Patient:200, SUM(name) AS Count
FROM tabConsultation;
(1054, u"Unknown column ‘Visits’ in ‘field list’")
SELECT patient AS Patient:Link/Patient:200, COUNT(name) AS Visits
FROM tabConsultation
GROUP BY patient
UNION
SELECT “all” AS Patient:Link/Patient:200, SUM(Visits) AS Visits
FROM tabConsultation
SELECT `patient` AS `Patient:Link/Patient:200`, COUNT(`name`) AS `Visits`
FROM `tabConsultation`
GROUP BY `patient`
UNION
SELECT "all" AS `Patient:Link/Patient:200`, COUNT(`name`) AS `Visits`
FROM `tabConsultation`;
How can we use filter in query report
For example.
It’s like the filter fields in the report where we can for example filter based months as a time
Or filter based on location.
Because I noticed that the query report give us Template which has only one result
But if we add to the query the ability of filtration. So the report will be dynamically result change🔀 based on user filter need.