Sum and Count of fields in one Query (SQL for Access)

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 ???
LVL 1
mADOchistAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Arthur_WoodCommented:
your table design is faulty.  You should have at least two tables, tblSale and tblSaleProduct. and more likely three, with tblProduct added.

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
0
mADOchistAuthor Commented:
It always has 6, if it has less the unwanted features get a 0...I am asking this question for a friend. Personally, I also found it strange to have enumerated fields in a table. But my friend told me that it cannot go higher than 6 and if it's lower then he wants the "unwanted features set to zero". Is there a way to do this even if you don't seem to agree with the structure ?. I don't know much about his program so I can't tell him what to do nor can he go back and change because he is in the late stages of his development.
0
Arthur_WoodCommented:
is it always the SAME 6 products, in precisly the same order, EVERY time?
0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

mADOchistAuthor Commented:
yes
0
Dishan FernandoSoftware Engineer / DBACommented:
Is this you want??

SELECT SUM(Product1),SUM(Product2),SUM(Product3),SUM(Product4),SUM(Product5),SUM(Product6)
FROM tblTransaction
0
TommyV10Commented:
I think this is the query you are looking for...

SELECT "Product1" AS Label, Sum(tblTransaction.Product1) AS Sum, Count(tblTransaction.Product1) AS Count
FROM tblTransaction
WHERE (tblTransaction.Product1)>0
UNION ALL
SELECT "Product2" AS Label, Sum(tblTransaction.Product2), Count(tblTransaction.Product2)
FROM tblTransaction
WHERE (tblTransaction.Product2)>0
UNION ALL
SELECT "Product3" AS Label, Sum(tblTransaction.Product3), Count(tblTransaction.Product3)
FROM tblTransaction
WHERE (tblTransaction.Product3)>0
UNION ALL
SELECT "Product4" AS Label, Sum(tblTransaction.Product4), Count(tblTransaction.Product4)
FROM tblTransaction
WHERE (tblTransaction.Product4)>0
UNION ALL
SELECT "Product5" AS Label, Sum(tblTransaction.Product5), Count(tblTransaction.Product5)
FROM tblTransaction
WHERE (tblTransaction.Product5)>0
UNION ALL
SELECT "Product6" AS Label, Sum(tblTransaction.Product6), Count(tblTransaction.Product6)
FROM tblTransaction
WHERE (tblTransaction.Product6)>0
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gajender_99Commented:
try this
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
0
Arthur_WoodCommented:
as I said: "and in answer to your question, it might be, but it would be a VERY clumsy query, involving UNIONs to get the values."

AW
0
syed555Commented:
try this one...
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
0
smartakusCommented:
Try this

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
0
mADOchistAuthor Commented:
Arthur_Wood : I agree with you that the structure is very "static" and is unappropriate. I suggested the change to my friend. The Unipn query worked so I awarded the points to TommyV10. I hope he changes the structure because I agree that it's a bad structure.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.