Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2003-11-26
11
Medium Priority
?
2,067 Views
Last Modified: 2006-11-17
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
Comment
Question by:mADOchist
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 9829615
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
 
LVL 1

Author Comment

by:mADOchist
ID: 9829696
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
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 9829749
is it always the SAME 6 products, in precisly the same order, EVERY time?
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 1

Author Comment

by:mADOchist
ID: 9829817
yes
0
 
LVL 8

Expert Comment

by:Dishan Fernando
ID: 9829949
Is this you want??

SELECT SUM(Product1),SUM(Product2),SUM(Product3),SUM(Product4),SUM(Product5),SUM(Product6)
FROM tblTransaction
0
 
LVL 1

Accepted Solution

by:
TommyV10 earned 320 total points
ID: 9830772
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
 
LVL 8

Expert Comment

by:gajender_99
ID: 9830806
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
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 9831785
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
 
LVL 2

Expert Comment

by:syed555
ID: 9844999
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
 

Expert Comment

by:smartakus
ID: 9850153
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
 
LVL 1

Author Comment

by:mADOchist
ID: 9853434
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question