Group By Total

[hackhack]

Hi Team,


This is my first post.

I have table as

Item1	Item2	Item3	Item1Qty	Item2Qty	Item2Qty
1	2	3	8	6	10
4	1	5	9	9	23
7	1	5	69	26	51

Now I want a report in this format

Item	Qty(Sum Item Qty)
1	86
2	6
3	10
5	74

Thank in Advance

[Micron]

Your table design doesn't look normalized in the least. Suggest you review database normalization before you go too far, otherwise everything you want to get out of it will likely be a real chore. The 1,4 and 7 in the item1 field don't make any sense to me either. You want item 5 total of 74, yet you have no item field for this, so the numbers don't make much sense. However, that's of little consequence; the lack of normalization is much more important. To get any advice on this from the forum, I think you'll have to supply more information about what you're doing, but if you review the subject first, you will be able to ask better questions thus get better advice.

[hackhack]

Your table design doesn't look normalized in the least. Suggest you review database normalization before you go too far, otherwise everything you want to get out of it will likely be a real chore. The 1,4 and 7 in the item1 field don't make any sense to me either. You want item 5 total of 74, yet you have no item field for this, so the numbers don't make much sense. However, that's of little consequence; the lack of normalization is much more important. To get any advice on this from the forum, I think you'll have to supply more information about what you're doing, but if you review the subject first, you will be able to ask better questions thus get better advice.

Thanks for the reply.These tables are create long back and I cannot change this as this contains huge amount of Data.



1 = 86 is the sum of item 1


So in the table

Item1	Item2	Item3	Item1Qty	Item2Qty	Item2Qty
1			8
	1			9
	1			26

Highlighted in Bold(Sum of Highlighted Items)

[June7]

8 + 9 + 26 = 43

8 + 9 + 69 = 86

I presume the second Item2Qty should be Item3Qty. Why did you exclude items 4 and 7 from the totals example?

I agree with micron. Data structure is a mess. However, messes can be dealt with, not always simple but can be done. Consider:

Query1:

SELECT 1 AS Source, Item1 AS ItemNum, Item1Qty AS Qty FROM tablename
UNION SELECT 2, Item2, Item2Qty FROM tablename
UNION SELECT 3, Item3, Item3Qty FROM tablename;

Query2:

SELECT ItemNum, Sum(Qty) AS SumQty FROM Query1 GROUP BY ItemNum;


The UNION query rearranges the data into normalized structure should have been to begin with. There is no builder or wizard for UNION query. Must type or copy/paste in query SQL View. Limit of 50 SELECT lines.

[hackhack]

8 + 9 + 26 = 43

8 + 9 + 69 = 86

I presume the second Item2Qty should be Item3Qty. Why did you exclude items 4 and 7 from the totals example?

I agree with micron. Data structure is a mess. However, messes can be dealt with, not always simple but can be done. Consider:

Query1:

SELECT 1 AS Source, Item1 AS ItemNum, Item1Qty AS Qty FROM tablename
UNION SELECT 2, Item2, Item2Qty FROM tablename
UNION SELECT 3, Item3, Item3Qty FROM tablename;

Query2:

SELECT ItemNum, Sum(Qty) AS SumQty FROM Query1 GROUP BY ItemNum;


The UNION query rearranges the data into normalized structure should have been to begin with. There is no builder or wizard for UNION query. Must type or copy/paste in query SQL View. Limit of 50 SELECT lines.

Thanks for the reply. I will check.