Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

I have a table on Transaction Sales

Here is the structure of my table

tblTransaction

--------------

TID

TDate

Product1

Product2

Product3

Product4

Product5

Product6

A Transaction ALWAYS includes 6 Products. If a product was not purchased during a transaction

Then it's value is set to 0.

I want to display the SUM and the COUNT of each Product in one single Query

When the Value of ProductX is 0 then I don't cumulate it in COUNT.

Here is a desired Display example:

Product 1 = Sum ; Count

Product 2 = Sum ; Count

Product 3 = Sum ; Count

Product 4 = Sum ; Count

....etc.

Is this possible in one Query ???

Here is the structure of my table

tblTransaction

--------------

TID

TDate

Product1

Product2

Product3

Product4

Product5

Product6

A Transaction ALWAYS includes 6 Products. If a product was not purchased during a transaction

Then it's value is set to 0.

I want to display the SUM and the COUNT of each Product in one single Query

When the Value of ProductX is 0 then I don't cumulate it in COUNT.

Here is a desired Display example:

Product 1 = Sum ; Count

Product 2 = Sum ; Count

Product 3 = Sum ; Count

Product 4 = Sum ; Count

....etc.

Is this possible in one Query ???

tbSales

---------

TID

TDate

tblProduct

-----------

PID

ProductName

Price

tblSaleProduct

---------------

TID

PID

then your app can easily keep track of which products were sold in each sale, and can ealiy generate the Totals and Counts that you want.

Are the 6 products that you show the ONLY possible Products (1 through 6) and always entered in that order?

and in answe to your question, it might be, but it would be a VERY clumsy query, involving UNIONs to get the values.

AW

SELECT SUM(Product1),SUM(Product2

FROM tblTransaction

select 'Product1 ='+str(sum(product1)) as prod_sum,count(*) as prod_count

from table12

where product1>0

union

select 'Product2 ='+str(sum(product2)) as prod_sum,count(*) as prod_count

from table12

where product2>0

union

select 'Product3 ='+str(sum(product3)) as prod_sum,count(*) as prod_count

from table12

where product3>0

union

select 'Product4 ='+str(sum(product4)) as prod_sum,count(*) as prod_count

from table12

where product4>0

union

select 'Product5 ='+str(sum(product5)) as prod_sum,count(*) as prod_count

from table12

where product5>0

union

select 'Product6 ='+str(sum(product6)) as prod_sum,count(*) as prod_count

from table12

where product6>0;

and this would show the result as

Prod_sum prod_count

---------- -------------

Product1 = 4 2

Product2 = 4 2

Product3 = 10 3

Product4 = 12 3

Product5 = 12 3

Product6 = 4 2

AW

if you dont want union then you can get the query horizontal this way.

SELECT Sum(prod1), -sum(prod1<>0) as CountOfPord1,

Sum(prod2), -sum(prod2<>0) as CountOfPord2,

Sum(prod3), -sum(prod3<>0) as CountOfPord3,

Sum(prod4), -sum(prod4<>0) as CountOfPord4, ..

FROM it;

Note: the above one is tested only in Access database

Syed

select prod + " = " + str(sum(suma)) + " ; " + str(sum(cant)) from

(

select "Product1" as prod, product1 as suma, iif(product1>0,1,0) as cant from tblTransaction

union all

select "Product2" as prod, product2 as suma, iif(product2>0,1,0) as cant from tblTransaction

union all

select "Product3" as prod, product3 as suma, iif(product3>0,1,0) as cant from tblTransaction

union all

select "Product4" as prod, product4 as suma, iif(product4>0,1,0) as cant from tblTransaction

union all

select "Product5" as prod, product5 as suma, iif(product5>0,1,0) as cant from tblTransaction

union all

select "Product6" as prod, product6 as suma, iif(product6>0,1,0) as cant from tblTransaction

)

group by prod

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

SELECT "Product1" AS Label, Sum(tblTransaction.Product

FROM tblTransaction

WHERE (tblTransaction.Product1)>

UNION ALL

SELECT "Product2" AS Label, Sum(tblTransaction.Product

FROM tblTransaction

WHERE (tblTransaction.Product2)>

UNION ALL

SELECT "Product3" AS Label, Sum(tblTransaction.Product

FROM tblTransaction

WHERE (tblTransaction.Product3)>

UNION ALL

SELECT "Product4" AS Label, Sum(tblTransaction.Product

FROM tblTransaction

WHERE (tblTransaction.Product4)>

UNION ALL

SELECT "Product5" AS Label, Sum(tblTransaction.Product

FROM tblTransaction

WHERE (tblTransaction.Product5)>

UNION ALL

SELECT "Product6" AS Label, Sum(tblTransaction.Product

FROM tblTransaction

WHERE (tblTransaction.Product6)>