COUNT(*)SELECT
case when id in (1,2,3,4) then 'Начальная школа'
when id in (5,6,7,8,9) then 'Средняя школа'
when id in (10,11) then 'Старшая школа'
end AS Class_type,
(
SELECT COUNT(*)
FROM class cc
WHERE (
case when сс.id in (1,2,3,4,5) then 'Начальная школа'
when cc.id in (6,7,8,9) then 'Средняя школа'
when cc.id in (10,11) then 'Старшая школа'
) = (
case when c.id in (1,2,3,4) then 'Начальная школа'
when c.id in (5,6,7,8,9) then 'Средняя школа'
when c.id in (10,11) then 'Старшая школа'
)
),
SUM(case when s.ocenka>4 then 1 end) AS 'Отличники'
FROM class c
GROUP BY Class_typeset isql="C:\\Program Files(x86)\\FireBird\\FireBird_2_1\\BIN\\isql.exe"
echo ^
SELECT C.CODE AS "Табельный", C.NAME AS "Ф.И.О.", T.TRANZDATE AS "Дата", max(T.TRANZTIME) AS "Время", T.INFOSTR AS "Карта" sum(T.SUMM) AS "Сумма" FROM DOCUMENT D LEFT JOIN TRANZT T ON D.ID = T.DOCUMENTID JOIN CLIENT C ON D.CLIENTID = C.ID WHERE T.TRANZDATE = cast('now' as date) and D.STATE = 1 AND D.ISFISCAL = 1 AND D.CLIENTID >=0 AND T.TRANZTYPE = '36' GROUP BY C.CODE, C.NAME, T.TRANZDATE, T.INFOSTR; ^
| %isql% 127.0.0.1/3050:E:\Torgovlya54\MAIN.GDB sysdba masterkey > outputfile.txtSELECT
C.CODE AS "Табельный",
C.NAME AS "Ф.И.О.",
T.TRANZDATE AS "Дата",
max(T.TRANZTIME) AS "Время",
T.INFOSTR AS "Карта",
sum(T.SUMM) AS "Сумма"
FROM
DOCUMENT D
LEFT JOIN TRANZT T ON D.ID = T.DOCUMENTID
JOIN CLIENT C ON D.CLIENTID = C.ID
WHERE
T.TRANZDATE >='20.02.2020' AND T.TRANZDATE <='20.02.2020' AND
T.TRANZTIME >='18:55:00' AND T.TRANZTIME <='23:59:59' AND
D.STATE = 1 AND
D.ISFISCAL = 1 AND
D.CLIENTID >=0 AND
T.TRANZTYPE = '36'
GROUP BY
C.CODE,
C.NAME,
T.TRANZDATE,
-- T.TRANZTIME,
T.INFOSTR
-- ,T.SUMM