A)

SELECT
uskid,
(SELECT COUNT(*) FROM T WHERE uskid=TT.uskid AND fm=2),
(SELECT COUNT(*) FROM T WHERE uskid=TT.uskid AND fm=3),
(SELECT COUNT(*) FROM T WHERE uskid=TT.uskid AND fm=4),
(SELECT COUNT(*) FROM T WHERE uskid=TT.uskid AND fm2=2),
(SELECT COUNT(*) FROM T WHERE uskid=TT.uskid AND fm2=3),
(SELECT COUNT(*) FROM T WHERE uskid=TT.uskid AND fm2=4)
FROM T TT
GROUP BY uskid;

B)

SELECT
uskid,
COUNT(CASE WHEN fm=2 THEN 1 END),
COUNT(CASE WHEN fm=3 THEN 1 END),
COUNT(CASE WHEN fm=4 THEN 1 END),
COUNT(CASE WHEN fm2=2 THEN 1 END),
COUNT(CASE WHEN fm2=3 THEN 1 END),
COUNT(CASE WHEN fm2=4 THEN 1 END)
FROM T
GROUP BY uskid;

(B should be faster, since it requires only one pass)


Ivan
http://www.volny.cz/iprenosil/interbase/

----- Original Message -----
From: "diwic2" <diwic2@...>
To: <firebird-support@yahoogroups.com>
Sent: Friday, March 04, 2005 4:25 PM
Subject: [firebird-support] Summary SQL question


>
>
> I have this table, and Firebird 1.5.0:
>
> uskid fm fm2
> 1 3 4
> 1 3 4
> 1 4 4
> 2 2 3
> 2 4 3
> 3 3 2
>
> I want some way to know how many 2:s, 3:s and 4:s there is in column
> fm and fm2, per uskid.
>
> Something like:
>
> select uskid, count(fm = 2), count(fm = 3), count(fm = 4),
> count(fm2 = 2), count(fm2 = 3), count(fm2 = 4)
> from mytable
> group by uskid
>
> The expected result would be:
> 1, 0, 2, 1, 0, 0, 3
> 2, 1, 0, 1, 0, 2, 0
> 3, 0, 1, 0, 1, 0, 0
>
> Of course, that's not valid SQL syntax, but I guess you understand how
> I mean. How do I do it?
>
> // David