[ic] SIMPLE SQL QUERY

videotaxi.nuernberg videotaxi.nuernberg at t-online.de
Fri Oct 31 11:53:01 EST 2003


> Hi everybody,
>
> i am using ic 4.87 and have encountered a simple problem,
> which i can't seem to solve, it's ridiculous ... maybe it's
> time for a break
>
> i have a prod_group column (FORD, BMW, PONTIAC ...) and a
> category (new, used, wreck ...) column all I want to show using SQL is
>
> FORD    new(5)    used(2)
> BMW wreck(1)
> PONTIAC used(5) wreck(9)
> ....
>
> I have tried using 'Group by' and 'Count' but I don't get it
> right, because if I group them by prod_group, i can't get to
> the category, and the other way around ??
>
> any ideas,
> a link to a similar question (and answer) would be great too,
> since i don't know what exactely to look for in the forum ...
>
> regards
>
> Steve
>
>Haven't tried it in Interchange, but the SQL statement
>SELECT Products.prod_group, Products.category, Count(*) as total
>FROM Products
>GROUP BY Products.prod_group, Products.category;
>
>should do what you want.
>
>Regards,
>
>John.


Hi Steve,

try something like this with ITL language


[query list=html ml=5000 st=sql sql="SELECT products.comb_category,
products.collection, products.category, COUNT(*) as count FROM products
WHERE groups = [scratch header] GROUP BY products.prod_group,
products.category, products.collection ORDER BY category"]

[list][sql-change 1][condition][sql-param category][/condition][sql-param
category]<br>[/sql-change 1]--[sql-param collection]&nbsp;([sql-param
count])<br>[/list]

[/query]


regards

joe





More information about the interchange-users mailing list