dla postgresa:
#!/bin/bash
/usr/local/pgsql/bin/psql -d lms -U lms -c \ "SELECT users.name || ' '|| users.lastname AS owner, inet_ntoa(ipaddr) AS ip, round(COALESCE(SUM((type * -2 + 7) * value), 0.00)/(CASE COUNT(DISTINCT nodes.id) WHEN 0 THEN 1 ELSE COUNT(DISTINCT nodes.id) END),2) AS balance FROM nodes LEFT JOIN users ON (ownerid = users.id) LEFT JOIN cash ON (cash.userid = users.id) GROUP BY nodes.id, ipaddr, users.lastname, users.name HAVING SUM((type * -2 + 7) * value) < 0"
bez postgresa:
#!/bin/bash mysql -D nazwabazy -e \ "SELECT users.lastname AS owner, inet_ntoa(ipaddr) AS ip, round(COALESCE(SUM((type * -2 + 7) * value), 0.00)/(CASE COUNT(DISTINCT nodes.id) WHEN 0 THEN 1 ELSE COUNT(DISTINCT nodes.id) END),2) AS balance
FROM nodes LEFT JOIN users ON (ownerid = users.id)
LEFT JOIN cash ON (cash.userid = users.id) GROUP BY nodes.id, ipaddr, users.lastname, users.name HAVING SUM((type * -2 + 7) * value) < 0"
i nawet dziala ;)