Solved

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

Posted on 2003-11-26
11
1,991 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
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
 
LVL 1

Author Comment

by:mADOchist
ID: 9829817
yes
0
 
LVL 8

Expert Comment

by:dishanf
ID: 9829949
Is this you want??

SELECT SUM(Product1),SUM(Product2),SUM(Product3),SUM(Product4),SUM(Product5),SUM(Product6)
FROM tblTransaction
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 1

Accepted Solution

by:
TommyV10 earned 80 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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

744 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now