Query Help - 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)

Query Help

Author

Topic

michaelb
Yak Posting Veteran

69 Posts

Posted - 2009-04-14 : 22:20:30

Hi,

I have the following query, but I need to somehow avoid grouping by t2.u_itemdiv, as this returns a row for each, but I want to combine it all into one row.

Any ideas?

Thanks!


select	t0.docdate as 'DATE',
		t1.baseref as 'ORDER',
		t0.docnum as 'INVOICE',
		case when t2.u_itemdiv = 'GLOVES' OR t2.u_itemdiv = 'PROVAL' then count(t1.itemcode) else 0 end as 'PROVAL',
		case when t2.u_itemdiv = 'METALMATE' then count(t1.itemcode) else 0 end as 'METALMATE',
		case when t2.u_itemdiv = 'DE SOLV IT' then count(t1.itemcode) else 0 end as 'DE SOLV IT',
		case when t2.u_itemdiv = 'HOTTIE''S' then count(t1.itemcode) else 0 end as 'HOTTIE''S',
		count(t1.itemcode) as 'TOTAL'

from	sborcr_live..oinv t0
		inner join
		sborcr_live..inv1 t1
		on t0.docentry = t1.docentry
		inner join
		sborcr_live..oitm t2
		on t1.itemcode = t2.itemcode

		where t0.docnum = '529160'
	
		group by	t1.baseref,
					t0.docnum,
					t2.u_itemdiv,
					t0.docdate

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-04-14 : 22:28:33

[code]select t0.docdate as 'DATE',
t1.baseref as 'ORDER',
t0.docnum as 'INVOICE',
Count(case when t2.u_itemdiv = 'GLOVES' OR t2.u_itemdiv = 'PROVAL' then t1.itemcode else 0 end) as 'PROVAL',
Count(case when t2.u_itemdiv = 'METALMATE' then t1.itemcode else 0 end) as 'METALMATE',
Count(case when t2.u_itemdiv = 'DE SOLV IT' then t1.itemcode else 0 end) as 'DE SOLV IT',
Count(case when t2.u_itemdiv = 'HOTTIE''S' then t1.itemcode else 0 end) as 'HOTTIE''S',
Count(t1.itemcode) as 'TOTAL'

from sborcr_live..oinv t0
inner join
sborcr_live..inv1 t1
on t0.docentry = t1.docentry
inner join
sborcr_live..oitm t2
on t1.itemcode = t2.itemcode

where t0.docnum = '529160'

group by t1.baseref,
t0.docnum,
t0.docdate[/code]

michaelb
Yak Posting Veteran

69 Posts

Posted - 2009-04-14 : 22:31:32

Thanks, but that gives me the 'total' for each of them, it's not counting each one individually.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-04-14 : 22:32:08

If you want to combine in 1 rows:

 select	t0.docdate as 'DATE',
		t1.baseref as 'ORDER',
		t0.docnum as 'INVOICE',
		Count(case when t2.u_itemdiv in('GLOVES','PROVAL' 'METALMATE','DE SOLV IT','HOTTIE''S') then t1.itemcode else 0 end) as 'METALMATE',
		Count(t1.itemcode) as 'TOTAL'

from	sborcr_live..oinv t0
		inner join
		sborcr_live..inv1 t1
		on t0.docentry = t1.docentry
		inner join
		sborcr_live..oitm t2
		on t1.itemcode = t2.itemcode

		where t0.docnum = '529160'
	
		group by	t1.baseref,
					t0.docnum,
					t0.docdate

michaelb
Yak Posting Veteran

69 Posts

Posted - 2009-04-14 : 22:34:25

I need a column for Proval, one for Metalmate, one for DeSolvIt and one for Hottie's. I need the count of itemcode for each of them. All this information in a single row but seperate columns.

Cheers

michaelb
Yak Posting Veteran

69 Posts

Posted - 2009-04-14 : 22:35:48

I have solved by replacing your 0 with NULL.

Thanks for your assistance!

select	t0.docdate as 'DATE',
		t1.baseref as 'ORDER',
		t0.docnum as 'INVOICE',
		Count(case when t2.u_itemdiv = 'GLOVES' OR t2.u_itemdiv = 'PROVAL' then t1.itemcode  else NULL end) as 'PROVAL',
		Count(case when t2.u_itemdiv = 'METALMATE' then t1.itemcode else NULL end) as 'METALMATE',
		Count(case when t2.u_itemdiv = 'DE SOLV IT' then t1.itemcode else NULL end) as 'DE SOLV IT',
		Count(case when t2.u_itemdiv = 'HOTTIE''S' then t1.itemcode else NULL end) as 'HOTTIE''S',
		Count(t1.itemcode) as 'TOTAL'

from	sborcr_live..oinv t0
		inner join
		sborcr_live..inv1 t1
		on t0.docentry = t1.docentry
		inner join
		sborcr_live..oitm t2
		on t1.itemcode = t2.itemcode

		where t0.docnum = '529160'
	
		group by	t1.baseref,
					t0.docnum,
					t0.docdate

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-04-14 : 22:37:55

Other way:

select	t0.docdate as 'DATE',
		t1.baseref as 'ORDER',
		t0.docnum as 'INVOICE',
		SUM(case when t2.u_itemdiv = 'GLOVES' OR t2.u_itemdiv = 'PROVAL' then 1 else 0 end) as 'PROVAL',
		SUM(case when t2.u_itemdiv = 'METALMATE' then 1 else 0 end) as 'METALMATE',
		SUM(case when t2.u_itemdiv = 'DE SOLV IT' then 1 else 0 end) as 'DE SOLV IT',
		SUM(case when t2.u_itemdiv = 'HOTTIE''S' then 1 else 0 end) as 'HOTTIE''S',
		COUNT(t1.itemcode) as 'TOTAL'

from	sborcr_live..oinv t0
		inner join
		sborcr_live..inv1 t1
		on t0.docentry = t1.docentry
		inner join
		sborcr_live..oitm t2
		on t1.itemcode = t2.itemcode

		where t0.docnum = '529160'
	
		group by	t1.baseref,
					t0.docnum,
					t0.docdate

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources