Weird Group by - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Weird Group by

Author

Topic

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-18 : 00:31:38

I have generated some query that return the data in TABLEAA, but i don't know how to group them up

CREATE TABLE [dbo].[TableAA](
	[Num] [nchar](10) NULL,
	[Alpha] [nchar](10) NULL,
	[Quantity] [int] NULL
) ON [PRIMARY]

INSERT INTO TABLEAA(Num,Alpha,Quantity)
SELECT 1, NULL, 1 UNION ALL
SELECT 2, NULL, 1 UNION ALL
SELECT 3, NULL, 1 UNION ALL
SELECT 4, NULL, 1 UNION ALL
SELECT 5, NULL, 1 UNION ALL
SELECT 6, 'A', 1 UNION ALL
SELECT 7, 'A', 1 UNION ALL
SELECT 8, 'B', 1 UNION ALL
SELECT 9, 'B', 1 UNION ALL
SELECT 10, 'C', 1 UNION ALL
SELECT 11, 'C', 1 UNION ALL
SELECT 12, 'C', 1 UNION ALL
SELECT 13, 'D', 1

how i can get the following result
GROUP	SUM(quantity)
NULL	1 <--- Num is 2 but don group by
A	6
B	2
C	3
D	1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-18 : 00:59:07

Please explain how sum for alpha A can be 6.

select alpha, sum(quantity) from tableaa group by alpha order by alpha

E 12°55'05.63"
N 56°04'39.26"

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-18 : 02:09:48

woops.. i forgot to mention how sumA is 6...sumA is 6 is because of sum of following, sorry


SELECT 1, NULL, 1 UNION ALL
SELECT 3, NULL, 1 UNION ALL
SELECT 4, NULL, 1 UNION ALL
SELECT 5, NULL, 1 UNION ALL
SELECT 6, 'A', 1 UNION ALL
SELECT 7, 'A', 1 UNION ALL

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-18 : 02:35:51

Why is it so hard to explain the business rules for summing the sample data?
For example, should this sample data return a sum of 4 for C?

INSERT INTO TABLEAA(Num,Alpha,Quantity)
SELECT 1, NULL, 1 UNION ALL
SELECT 2, NULL, 1 UNION ALL
SELECT 3, NULL, 1 UNION ALL
SELECT 4, NULL, 1 UNION ALL
SELECT 5, 'A', 1 UNION ALL
SELECT 6, 'A', 1 UNION ALL
SELECT 7, 'B', 1 UNION ALL
SELECT 8, 'B', 1 UNION ALL
SELECT 9, NULL, 1 UNION ALL
SELECT 10, 'C', 1 UNION ALL
SELECT 11, 'C', 1 UNION ALL
SELECT 12, 'C', 1 UNION ALL
SELECT 13, 'D', 1

E 12°55'05.63"
N 56°04'39.26"

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-18 : 02:42:27

if according to urs sample data and my requirement, the output data will be

NULL 1 <- where by Num2 will group by them first cause sometime they duplicate
A    6
B    2
C    3
D    1

INSERT INTO TABLEAA(Num,Alpha,Quantity)
SELECT 1, NULL, 1 UNION ALL
SELECT 2, NULL, 1 UNION ALL
SELECT 2, NULL, 1 UNION ALL
SELECT 2, NULL, 1 UNION ALL
SELECT 2, NULL, 1 UNION ALL
SELECT 3, NULL, 1 UNION ALL
SELECT 4, NULL, 1 UNION ALL
SELECT 5, 'A', 1 UNION ALL
SELECT 6, 'A', 1 UNION ALL
SELECT 7, 'B', 1 UNION ALL
SELECT 8, 'B', 1 UNION ALL
SELECT 9, NULL, 1 UNION ALL
SELECT 10, 'C', 1 UNION ALL
SELECT 11, NULL, 1 UNION ALL
SELECT 12, 'C', 1 UNION ALL
SELECT 13, 'C', 1 UNION ALL
SELECT 14, NULL, 1 UNION ALL
SELECT 15, 'D', 1

Result will be
Null  4
A     8
B     2
C     3
D     1

[edit] wrong result, and additional sample

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-18 : 03:23:15

The reason i need this cause the result are the same for the following query without grouping

SELECT	t.st_code,
		c.cat_code, 
		c.cat_desc, total_rm, total_cn, qty
FROM	(SELECT st_code, 
				CASE WHEN cat_code IS NULL or cat_code = '' then '?Item Not Found?' else cat_code END as cat_code
		FROM st_mast
		UNION 
		SELECT	st_code,
				CASE WHEN cat_code IS NULL or cat_code = '' then '?Item Not Found?' else cat_code END as cat_code
		FROM st_msuspend)m left outer join category c on m.cat_code=c.cat_code
		right join
		(SELECT	st_code,
				SUM(total_rm*forex_rate) as total_rm,
				SUM(total_cn*forex_rate) as total_cn,
				SUM(qtt_out-quantity) as qty
		FROM(SELECT	CASE WHEN st_code IS NULL or st_code = '' then '?no code?' else st_code END as st_code,
					qtt_out, quantity,
					CASE WHEN h.forex_rate = 0 then 1 else h.forex_rate END as forex_rate,
					CASE WHEN t.trx_type IN('CN','CNC','GRO') then 0 else t.total_price END as total_rm,
					CASE WHEN t.trx_type IN('CN','CNC','GRO') then t.total_price else 0 END as total_cn
			FROM	st_trx t 
					right outer join (
					SELECT	CASE WHEN trx_type in('DO','CDO','DOL') then do_no
							WHEN trx_type in('INV','CS','CN','DN','POS','INC','CNC') then in_no END as ref_no,
							CASE WHEN trx_type in('DO','CDO','DOL') then (CASE WHEN in_date IS NULL or in_date='' then do_date else in_date END)
							WHEN trx_type in('INV','CS','CN','DN','POS','INC','CNC') then in_date END as date,
							forex_rate,
							trx_type
					FROM	st_head
					WHERE	trx_type IN('INV','CS','CN','DN','POS','INC','CNC','DO','CDO','DOL'))h on t.trnx_ref = h.ref_no and t.trx_type=h.trx_type
			where date>='20070101' and date<'20080101')a
		GROUP BY st_code)t on m.st_code=t.st_code
ORDER BY cat_code, st_code

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-18 : 03:44:20

IGNORE THIS POST

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-18 : 03:48:26

quote:

Originally posted by waterduck

SELECT 1, NULL, 1 UNION ALL
SELECT 2, NULL, 1 UNION ALL
SELECT 2, NULL, 1 UNION ALL
SELECT 2, NULL, 1 UNION ALL
SELECT 2, NULL, 1 UNION ALL
SELECT 3, NULL, 1 UNION ALL
SELECT 4, NULL, 1 UNION ALL
SELECT 5, 'A', 1 UNION ALL
SELECT 6, 'A', 1 UNION ALL
SELECT 7, 'B', 1 UNION ALL
SELECT 8, 'B', 1 UNION ALL
SELECT 9, NULL, 1 UNION ALL
SELECT 10, 'C', 1 UNION ALL
SELECT 11, NULL, 1 UNION ALL
SELECT 12, 'C', 1 UNION ALL
SELECT 13, 'C', 1 UNION ALL
SELECT 14, NULL, 1 UNION ALL
SELECT 15, 'D', 1

Result will be

Null  4
A     8
B     2
C     3
D     1

How is this possible?
NULL should be 7, right?
And A should be 2, right?

If not, PLEASE explain your business rules for summing the groups.
If you can't explain this, I think no one will be able to assist you.

E 12°55'05.63"
N 56°04'39.26"

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-18 : 03:58:49

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-18 : 04:03:50

What are the rules for NULL records belong to "A"?
Why not "B"?

E 12°55'05.63"
N 56°04'39.26"

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-18 : 04:12:43

Erm...it is actually like double condition
Lets assume that Num(2) is Unknown stock code with unknown category
however Group('NULL','A') is unknown category with known stock code
Group('B','C','D') will b known stock code and known category

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-18 : 04:17:52

This is fun!
This topic can go on forever and ever...

E 12°55'05.63"
N 56°04'39.26"

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-18 : 04:20:46

>"< thats why ignore it..just cursor it more less use brain

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-18 : 04:23:21