How to achieve count() from joined table (like GROUP BY in SQL)? RRS feed

  • Question

  • Hi,

    I have two tables, both share the column "cid", i.e. customer id.
    The datasource is not in SQL Server - it is saved as a file and loaded in memory at runtime.
    I am trying to output a list of customers with 3 fields: customer name, company, nr. of invoices.

    The SQL equivalent would be: SELECT Customers.cname, Customers.ccompany, count(Invoices.cid) AS "nrinv" FROM "Customers" LEFT JOIN "Invoices" USING(cid) GROUP BY cid
    My report has a table and I can list the first two fields, but I don't understand how to achieve the COUNT() from the joined table.

    The output should be like:

    Customer Name   Company        Nr. Invoices
    CUSTNAME1         COMPANY1         5
    CUSTNAME2         COMPANY2         0

    Please help, it's driving me nuts.

    Many thanks in advance,
    Wednesday, June 17, 2009 8:18 AM