?
Solved

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

Posted on 2003-11-26
11
Medium Priority
?
2,057 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
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
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…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

752 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