> 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 ;)
--
Drozd Rafal.
