How to write sql query in EditScript ??

[nikhilgarg]

Hey,

I have 3 columns say:  Item1 , Item2 , Item3. Now i have to count their numbers by writing SqlQueries.

In expression i have used Count(Item1Id), COunt(Item2Id) , Count(Item3Id).

In Script window how can i write the same in Sql query form?? Because if i write:

SQL Select count(Item1ID) from abc ; but what do i write in LOAD statement above it??

tHanks

[anbu1984]

Select Count(Item1) From abc; // This count wont match with Pivot table count. This count is for entire table and not for each region

Select Count(Item1) From abc Group by Dim1,Dim2; //Include all dimensions used in Pivot table. This result will match with Pivot table count

[nikhilgarg]

Hey,

Thanx a lot , it will help me but do you mean to say that in select query i

have to use dimensions in group by clause which are selected in pivot table

? Because in that cyclic grups are also used....

[maleksafa]

so in your pivot table you have region as dimension and count as expression.

in that case you sql must be:

select region, count(item1), count(item2), count(item3) from table group by region

it should give you the same result, make sure that you don't have any filtering applied in the chart.

[nikhilgarg]

Hey thanx but what you mean by filtering?? I eman i have multibox named SortBy where these number of counts are there as columns. Is this filtering you are talking about?

THanks

[maleksafa]

no, i was talking about the filters applied to the dashboard i.e selections.

[nikhilgarg]

Ohk. Actually, i see the columns on which i have to put GroupBy are coming from different tables. Now how can i put group by on those columns ??

[maleksafa]

can you please share some more information? probably upload a sample document.

[nikhilgarg]

Ohk,

In Pivot table, Dim are:  Col1 , Col2 , Col3, Col4

1:  Col1, Col2 , Col3 are from excel sheet. I Load it as :

Load Col1 , col2 , Col3 from ....

2:  COl4 is from OLEDB conn. I load it as:

LOAD Col4 from......

Now , i want count of item1 which is in other table. I load it as:

Load ITem1 , (ol other columns) from....

Now i have put count of item 1 in pivot table with dim Col1 , Col2 , Col3 , Col4. As per you , i ahve to put GroupBy clause. Now how can i put Group By clause on col 1 , col2 , col3 , col4 (which are coming from diff source) while calculating Count(Item)

[petter]

The

     Select count(item1) from .....   

Will give you a grand total.

A Pivot table will do a group by on the dimensions that you use in the pivot table - so if you have any dimensions at all in the pivot (which I think you must have) you should get a different result.