[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2081
  • Last Modified:

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 ???
0
mADOchist
Asked:
mADOchist
1 Solution
 
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now