yes i got it thanks a lot for your time and effort

Original Message:

Sent: 10-23-2019 05:11

From: Candido Dessanti

Subject: join issue between tables

Hi @Nagulan Santhi,

the values are changing because of the count distinct of a column will count the distinct values present in a group/table, regardless of the number of rows in the group, while the count(*) is going to return the number of rows in that group regardless of the contents of the columns in the rows.

using you query without grouping

SELECT m5dad587e_product.rowid AS product_rowid, m5dad587e_product".m_cost as product_cost, "m5dad587e_purchase_order.rowid" as order_rowidFROM "m5dad587e_product" JOIN m5dad587e_purchase_order ON "m5dad587e_product".m_product_id = "m5dad587e_purchase_order".m_product_id WHERE "m5dad587e_purchase_order".m_order_date BETWEEN '2019-01-01' AND '2019-12-31' AND "m5dad587e_product".m_product_id = 'ARGBDGHNJ17BRRMLB'

will return

product_rowid|product_cost|order_rowid476 |25 |102476 |25 |134476 |25 |153476 |25 |167476 |25 |178476 |25 |320

Asking a count distinct of product_rowid, the database will return the number of distinct values on that row; in this case all of them are 476, so it will return 1

while asking a count(*) it will return the number of rows in the resultset, so 7.

Original Message:

Sent: 10-22-2019 10:38

From: Nagulan Santhi

Subject: join issue between tables

Hi @Candido Dessanti

In mapd rowid is unique number which has for all rows including duplicate rows but why i am getting count(*) as 7 and count(rowid) as 1

for below queries

SELECT count(distinct m5dad587e_product.rowid) AS "label_product.cost" FROM "m5dad587e_product" JOIN m5dad587e_purchase_order ON "m5dad587e_product".m_product_id = "m5dad587e_purchase_order".m_product_id WHERE "m5dad587e_purchase_order".m_order_date BETWEEN '2019-01-01' AND '2019-12-31' AND "m5dad587e_product".m_product_id = 'ARGBDGHNJ17BRRMLB'

i am getting count as 1

SELECT count(*) AS "label_product.cost" FROM "m5dad587e_product" JOIN m5dad587e_purchase_order ON "m5dad587e_product".m_product_id = "m5dad587e_purchase_order".m_product_id WHERE "m5dad587e_purchase_order".m_order_date BETWEEN '2019-01-01' AND '2019-12-31' AND "m5dad587e_product".m_product_id = 'ARGBDGHNJ17BRRMLB'

i am getting count as 7

in above all were same but count(*) value is differing from count(rowid)

Original Message:

Sent: 10-22-2019 09:48

From: Candido Dessanti

Subject: join issue between tables

Hi @Nagulan Santhi,

In Omnisci, like other databases, rowid isn't a function but a pseudo column; each row you insert in a table has a unique rowid. On omnisci is a progressive number starting from 1 up to the number of rows in the table.

I used rowid because I needed a unique identifier of the record in a particular table, without the need to know anything about that table, to spot, using the distinct clause, how many rows were coming from that table before the join.

In case you have unwanted duplication due to the join of two tables with different cardinality for the key used in the join, dividing the count() by the count(distinct rowid), return how many times that row got duplicated, so dividing the sum by this number, return the value you would get without the join.

Original Message:

Sent: 10-22-2019 09:13

From: Nagulan Santhi

Subject: join issue between tables

Hi @Candido Dessanti

thanks for your effort and time

can you explain rowid function in mapd because

SELECT count(distinct m5dad587e_product.rowid) AS "label_product.cost" FROM "m5dad587e_product" JOIN m5dad587e_purchase_order ON "m5dad587e_product".m_product_id = "m5dad587e_purchase_order".m_product_id WHERE "m5dad587e_purchase_order".m_order_date BETWEEN '2019-01-01' AND '2019-12-31' AND "m5dad587e_product".m_product_id = 'ARGBDGHNJ17BRRMLB'

i am getting count as 1

SELECT count(*) AS "label_product.cost" FROM "m5dad587e_product" JOIN m5dad587e_purchase_order ON "m5dad587e_product".m_product_id = "m5dad587e_purchase_order".m_product_id WHERE "m5dad587e_purchase_order".m_order_date BETWEEN '2019-01-01' AND '2019-12-31' AND "m5dad587e_product".m_product_id = 'ARGBDGHNJ17BRRMLB'

i am getting count as 7

i have checked all row as unique but i am getting rowid count as 1

Original Message:

Sent: 10-22-2019 06:12

From: Candido Dessanti

Subject: join issue between tables

If you need to sum duplicated data, you should just do the sum without any division.

btw aware about your data, and what you want to achieve with all you queries

Original Message:

Sent: 10-22-2019 05:59

From: Nagulan Santhi

Subject: join issue between tables

so how can we do for this case

Original Message:

Sent: 10-22-2019 05:52

From: Candido Dessanti

Subject: join issue between tables

Hi @Nagulan Santhi,

The count(*)/count(distinct ...) is needed when you need to sum some data ignoring the duplication due from the joins, but in this particular case you need to sum duplicated data, so you divide the summed data by anything.

Original Message:

Sent: 10-22-2019 05:13

From: Nagulan Santhi

Subject: join issue between tables

Hi @Candido Dessanti

i have done count(*)/count(distinct rowid) but result in getting differ in above query can you please check

Original Message:

Sent: 10-22-2019 04:00

From: Nagulan Santhi

Subject: join issue between tables

Hi @Candido Dessanti

i have created like this

SELECT CAST(sum(coalesce(round("m5dad587e_product".m_cost, 2), 0)) AS DOUBLE PRECISION) / (count(*)/(count(distinct "m5dad587e_product".rowid))) AS "label_product.cost", CAST(sum(coalesce(round("m5dad587e_sales_order".m_order_item_amount, 2), 0)) AS DOUBLE PRECISION) AS "label_sales_order.order_item_amount" FROM "m5dad587e_product" JOIN m5dad587e_sales_order ON "m5dad587e_product".m_product_id = "m5dad587e_sales_order".m_product_id WHERE "m5dad587e_sales_order".m_order_date BETWEEN '2019-01-01' AND '2019-12-31' AND "m5dad587e_product".m_product_id = 'ARGBDGHNJ17BRRMLB' LIMIT 1001

its giving as 4.5|4295.330000000001

i should get as 31.5 as cost value not 4.5

but for this

SELECT distinct(CAST(sum(coalesce(subquery."label_product.cost", 0)) AS DOUBLE PRECISION)) AS "label_product.cost" FROM (SELECT distinct(CAST(sum(coalesce(round("m5dad587e_product".m_cost, 2), 0)) AS DOUBLE PRECISION)) AS "label_product.cost" FROM "m5dad587e_product" JOIN m5dad587e_purchase_order ON "m5dad587e_product".m_product_id = "m5dad587e_purchase_order".m_product_id WHERE "m5dad587e_purchase_order".m_order_date BETWEEN '2019-01-01' AND '2019-12-31' AND "m5dad587e_product".m_product_id = 'ARGBDGHNJ17BRRMLB') AS subquery LIMIT 1001;

its giving 31.5 as cost

Original Message:

Sent: 10-22-2019 03:41

From: Candido Dessanti

Subject: join issue between tables

Hi,

You have to divide by (count(*) /count(distinct rowid of table where the measure is coming)).

Original Message:

Sent: 10-22-2019 03:28

From: Nagulan Santhi

Subject: join issue between tables

Hi @Candido Dessanti

```SELECT

CAST(sum(coalesce(round("m5dad587e_product".m_cost, 2), 0)) AS DOUBLE PRECISION) AS "label_product.cost", CAST(sum(coalesce(round("m5dad587e_sales_order".m_order_item_amount, 2), 0)) AS DOUBLE PRECISION) AS "label_sales_order.order_item_amount" FROM

"m5dad587e_product" JOIN m5dad587e_sales_order ON "m5dad587e_product".m_product_id = "m5dad587e_sales_order".m_product_id

WHERE "m5dad587e_sales_order".m_order_date BETWEEN '2019-01-01' AND '2019-12-31' AND "m5dad587e_product".m_product_id = 'ARGBDGHNJ17BRRMLB' LIMIT 1001```

in this query we have only two tables now i have tried / (count(distinct "m5dad587e_product".rowid) for label_product.cost but its giving worng result

i think this can be applied only if we have three tables in join, if we have two tables its getting wrong result so how to query this to avoid cardinality of the records

Original Message:

Sent: 10-21-2019 09:03

From: Candido Dessanti

Subject: join issue between tables

Hi @Nagulan Santhi,

the basic principle is to divide the summed value (this is going to work for sum only of course) by the total number of rows of divided by the resulting rows table.

so have three tables you are going to join and the cardinality is 2, 3 and 5 you have to divide the count(*) with the count distinct of that column.

so sum(table1)/(count(*)/count(distinct table1.rowid)),

sum(table2)/(count(*)/count(distinct table2.rowid)),

sum(table3)/(count(*)/count(distinct table3.rowid).

rewriting your query

SELECT CAST(sum(coalesce(round("m5d9f6525_PO_ITEM".m_totalcost, 2), 0)) AS DOUBLE PRECISION) / (count(*)/count(distinct "m5d9f6525_PO_ITEM".rowid)) AS "label_PO_ITEM.totalcost", CAST(sum(coalesce(round("m5d9f6525_SO_ITEM".m_totalprice, 2), 0)) AS DOUBLE PRECISION) / (count(*)/count(distinct "m5d9f6525_SO_ITEM".rowid)) AS "label_SO_ITEM.totalprice" FROM "m5d9f6525_PO_ITEM" JOIN m5d9f6525_part ON "m5d9f6525_PO_ITEM".m_partId = "m5d9f6525_part".m_id JOIN m5d9f6525_product ON "m5d9f6525_part".m_num = "m5d9f6525_product".m_product_short_num JOIN m5d9f6525_SO_ITEM ON "m5d9f6525_SO_ITEM".m_productId = "m5d9f6525_product".m_id WHERE "m5d9f6525_product".m_num = '200124' LIMIT 1001

I haven't tried, but it should work

Original Message:

Sent: 10-21-2019 07:17

From: Nagulan Santhi

Subject: join issue between tables

Hi @Candido Dessanti

Its working fine, Thanks for your idea and effort

but if i had about five to six tables can you suggest whether i can use this method

Original Message:

Sent: 10-21-2019 06:33

From: Candido Dessanti

Subject: join issue between tables

Ops...I used of rowid. The correct query would be this one

SELECT CAST(sum(coalesce(round("m5d9f6525_PO_ITEM".m_totalcost, 2), 0)) AS DOUBLE PRECISION) / count(distinct "m5d9f6525_SO_ITEM".rowid) AS "label_PO_ITEM.totalcost", CAST(sum(coalesce(round("m5d9f6525_SO_ITEM".m_totalprice, 2), 0)) AS DOUBLE PRECISION) / count(distinct "m5d9f6525_PO_ITEM".rowid) AS "label_SO_ITEM.totalprice" FROM "m5d9f6525_PO_ITEM" JOIN m5d9f6525_part ON "m5d9f6525_PO_ITEM".m_partId = "m5d9f6525_part".m_id JOIN m5d9f6525_product ON "m5d9f6525_part".m_num = "m5d9f6525_product".m_product_short_num JOIN m5d9f6525_SO_ITEM ON "m5d9f6525_SO_ITEM".m_productId = "m5d9f6525_product".m_id WHERE "m5d9f6525_product".m_num = '200124' LIMIT 1001

Original Message:

Sent: 10-21-2019 06:29

From: Nagulan Santhi

Subject: join issue between tables

Hi @Candido Dessanti

i am getting error as no column as rownum in "m5d9f6526_SO_ITEM"

how to use rownum function in mapd

Original Message:

Sent: 10-21-2019 05:15

From: Candido Dessanti

Subject: join issue between tables

HI @Nagulan Santhi,

product

the problem is about cardinality of data on different tables you are trying to join, so if in the product table we have 2 records for the product (sales) and 3 records for the item (costs), joining them would produce 6 row the group by, so the sum of sales going to be 3 times the real value while the costs 2 times

try this

SELECT CAST(sum(coalesce(round("m5d9f6525_PO_ITEM".m_totalcost, 2), 0)) AS DOUBLE PRECISION) / count(distinct "m5d9f6525_SO_ITEM".rownum) AS "label_PO_ITEM.totalcost", CAST(sum(coalesce(round("m5d9f6525_SO_ITEM".m_totalprice, 2), 0)) AS DOUBLE PRECISION) / count(distinct "m5d9f6525_PO_ITEM".rownum) AS "label_SO_ITEM.totalprice" FROM "m5d9f6525_PO_ITEM" JOIN m5d9f6525_part ON "m5d9f6525_PO_ITEM".m_partId = "m5d9f6525_part".m_id JOIN m5d9f6525_product ON "m5d9f6525_part".m_num = "m5d9f6525_product".m_product_short_num JOIN m5d9f6525_SO_ITEM ON "m5d9f6525_SO_ITEM".m_productId = "m5d9f6525_product".m_id WHERE "m5d9f6525_product".m_num = '200124' LIMIT 1001

Original Message:

Sent: 10-21-2019 04:14

From: Nagulan Santhi

Subject: join issue between tables

Hi @Candido Dessanti

Thanks for your effort but is there any way to use any other joins with a single query. because for it needs a lot of effort to redesign in coding. so can you help me out of this problem

Original Message:

Sent: 10-18-2019 10:59

From: Candido Dessanti

Subject: join issue between tables

Hi @Nagulan Santhi,

we could use window functions, but it can make some approximations and the performance could be worse.

If you need to query just each time, you can remove the query condition using a cross join.

To improve the readability of you can use with clause

with cost as ((select m_num, CAST(sum(coalesce(round("m5d9f6525_PO_ITEM".m_totalcost, 2), 0)) AS DOUBLE PRECISION) AS "label_PO_ITEM.totalcost" from "m5d9f6525_PO_ITEM" JOIN m5d9f6525_part ON "m5d9f6525_PO_ITEM".m_partId = "m5d9f6525_part".m_id where "m5d9f6525_part".m_num = '200124' group by m_num),revenue as (select m_num, CAST(sum(coalesce(round("m5d9f6525_SO_ITEM".m_totalprice, 2), 0)) AS DOUBLE PRECISION) AS "label_SO_ITEM.totalprice" from "m5d9f6525_SO_ITEM" JOIN m5d9f6525_product ON "m5d9f6525_SO_ITEM".m_productId = "m5d9f6525_product".m_id WHERE "m5d9f6525_product".m_num = '200124' group by m_num)select revenue.m_num, "label_PO_ITEM.totalcost", "label_SO_ITEM.totalprice" from cost join revenue on cost.m_num=revenue.m_num LIMIT 1001;

Original Message:

Sent: 10-18-2019 10:13

From: Nagulan Santhi

Subject: join issue between tables

Hi @Candido Dessanti

Thanks a lot its working fine

can we query this without using

Original Message:

Sent: 10-18-2019 09:33

From: Candido Dessanti

Subject: join issue between tables

Hi @Nagulan Santhi,

Probably because of the cardinality of the records before the sum is increasing, and you are going to sum a lot of duplicates values from both the tables.

A product's likely to have more than one part, so the duplicates.

Can you rewrite the query like this, and check if you are getting the right results

select revenue.m_num, "label_PO_ITEM.totalcost", "label_SO_ITEM.totalprice" from (select m_num, CAST(sum(coalesce(round("m5d9f6525_PO_ITEM".m_totalcost, 2), 0)) AS DOUBLE PRECISION) AS "label_PO_ITEM.totalcost" from "m5d9f6525_PO_ITEM" JOIN m5d9f6525_part ON "m5d9f6525_PO_ITEM".m_partId = "m5d9f6525_part".m_id where "m5d9f6525_part".m_num = '200124' group by m_num) as cost join (select m_num, CAST(sum(coalesce(round("m5d9f6525_SO_ITEM".m_totalprice, 2), 0)) AS DOUBLE PRECISION) AS "label_SO_ITEM.totalprice" from "m5d9f6525_SO_ITEM" JOIN m5d9f6525_product ON "m5d9f6525_SO_ITEM".m_productId = "m5d9f6525_product".m_id WHERE "m5d9f6525_product".m_num = '200124' group by m_num) as revenue on cost.m_num=revenue.m_num LIMIT 1001;

Original Message:

Sent: 10-18-2019 06:37

From: Nagulan Santhi

Subject: join issue between tables

i have three queries data is same but values is getting different from each:

1.spend for 200124:

SELECT distinct(CAST(sum(coalesce(subquery."label_PO_ITEM.totalcost", 0)) AS DOUBLE PRECISION)) AS "label_PO_ITEM.totalcost" FROM (SELECT distinct(CAST(sum(coalesce(round("m5d9f6525_PO_ITEM".m_totalcost, 2), 0)) AS DOUBLE PRECISION)) AS "label_PO_ITEM.totalcost" FROM "m5d9f6525_PO_ITEM" JOIN m5d9f6525_part ON "m5d9f6525_PO_ITEM".m_partId = "m5d9f6525_part".m_id WHERE "m5d9f6525_part".m_num = '200124') AS subquery LIMIT 1001

for am getting value as 144104.96

2.revenue for 200124:

SELECT distinct(CAST(sum(coalesce(subquery."label_SO_ITEM.totalprice", 0)) AS DOUBLE PRECISION)) AS "label_SO_ITEM.totalprice" FROM (SELECT distinct(CAST(sum(coalesce(round("m5d9f6525_SO_ITEM".m_totalprice, 2), 0)) AS DOUBLE PRECISION)) AS "label_SO_ITEM.totalprice" FROM "m5d9f6525_SO_ITEM" JOIN m5d9f6525_product ON "m5d9f6525_SO_ITEM".m_productId = "m5d9f6525_product".m_id WHERE "m5d9f6525_product".m_num = '200124') AS subquery LIMIT 1001

229865.06

3.spend revenue for 200124:

SELECT CAST(sum(coalesce(round("m5d9f6525_PO_ITEM".m_totalcost, 2), 0)) AS DOUBLE PRECISION) AS "label_PO_ITEM.totalcost", CAST(sum(coalesce(round("m5d9f6525_SO_ITEM".m_totalprice, 2), 0)) AS DOUBLE PRECISION) AS "label_SO_ITEM.totalprice" FROM "m5d9f6525_PO_ITEM" JOIN m5d9f6525_part ON "m5d9f6525_PO_ITEM".m_partId = "m5d9f6525_part".m_id JOIN m5d9f6525_product ON "m5d9f6525_part".m_num = "m5d9f6525_product".m_product_short_num JOIN m5d9f6525_SO_ITEM ON "m5d9f6525_SO_ITEM".m_productId = "m5d9f6525_product".m_id WHERE "m5d9f6525_product".m_num = '200124' LIMIT 1001

have combined first two queries so should result as 229865.06

but am getting as 29685621.76|13332173.48

guess this is due to join values is getting repeated for each join

how can combine first two so that can get (is there any way to query from different tables)

#Core

#Immerse