combine row results form query

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 2008 Forums

Transact-SQL (2008)

combine row results form query

Author

Topic

mikemcg36
Starting Member

9 Posts

Posted - 2011-11-02 : 11:27:40

If I have a query that spits out result like:

Store # # red sold # blue sold total
Store 1 2 0 2
Store 1 0 1 1
Store 2 1 1 2
Store 3 0 1 1

How would I combine the results for the duplicate stores? So the result would be:

Store # # red sold # blue sold total
Store 1 2 1 3
Store 2 1 1 2
Store 3 0 1 1

Do you need more detail to provide help? Let me know and I will supply code. Thanks.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-11-02 : 11:32:42

formatted...

Originally posted by mikemcg36

If I have a query that spits out result like: 

Store #		# red sold	# blue sold	total
Store 1		2		0		2
Store 1		0		1		1
Store 2		1		1		2
Store 3		0		1		1

How would I combine the results for the duplicate stores? So the result would be:

Store #		# red sold	# blue sold	total
Store 1		2		1		3
Store 2		1		1		2
Store 3		0		1		1

Do you need more detail to provide help? Let me know and I will supply code. Thanks.

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-11-02 : 11:35:20

select
[Store #],
sum([# red sold]) as [# red sold],
sum([# blue sold]) as [# blue sold],
sum(total) as total
from
(here_comes_your_query)dt
group by [Store #]

No, you're never too old to Yak'n'Roll if you're too young to die.

mikemcg36
Starting Member

9 Posts

Posted - 2011-11-02 : 12:09:00

I still get duplicate rows, here is my actual query:


SELECT
	Customer.CustomerName AS "Customer",
	SUM(case when BRND.Brand = '1311' then Data.Qty else 0 end) AS "Coke",
	SUM(case when BRND.Brand = '1313' then Data.Qty else 0 end) AS "pepsi"
FROM
	Customer,
	CustomerSales,
	Data,
	Brand,
	App2
WHERE
	Data.id = CustomerSales.id and
	CustomerSales.ID = Customer.ID and
	Data.Brand = Brand.Brand and
	APP2.Size = Data.Size and
	CustomerSales.Cust_type = 'L'
GROUP BY
	tableCustomer.Customer,
	tableCustomer.CustomerName,
	Brand.Brand,
	Data.Qty,
	App2.Milliliters
ORDER BY
	tableCustomer.CustomerName

mikemcg36
Starting Member

9 Posts

Posted - 2011-11-02 : 12:16:05

Sorry, I did not edit my query all the way, here it is updated:

quote:

Originally posted by mikemcg36

I still get duplicate rows, here is my actual query:


SELECT
	Customer.CustomerName AS "Customer",
	SUM(case when Brand.Brand = '1311' then Data.Qty else 0 end) AS "Coke",
	SUM(case when Brand.Brand = '1313' then Data.Qty else 0 end) AS "pepsi"
FROM
	Customer,
	CustomerSales,
	Data,
	Brand,
	App2
WHERE
	Data.id = CustomerSales.id and
	CustomerSales.ID = Customer.ID and
	Data.Brand = Brand.Brand and
	APP2.Size = Data.Size and
	CustomerSales.Cust_type = 'L'
GROUP BY
	Customer.CustomerName,
	Brand.Brand,
	Data.Qty,
	App2.Milliliters
ORDER BY
	Customer.CustomerName

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-11-02 : 14:23:55

Try only to group by CustomerName.

quote:

Originally posted by mikemcg36

I still get duplicate rows, here is my actual query:


SELECT
	Customer.CustomerName AS "Customer",
	SUM(case when BRND.Brand = '1311' then Data.Qty else 0 end) AS "Coke",
	SUM(case when BRND.Brand = '1313' then Data.Qty else 0 end) AS "pepsi"
FROM
	Customer,
	CustomerSales,
	Data,
	Brand,
	App2
WHERE
	Data.id = CustomerSales.id and
	CustomerSales.ID = Customer.ID and
	Data.Brand = Brand.Brand and
	APP2.Size = Data.Size and
	CustomerSales.Cust_type = 'L'
GROUP BY
	tableCustomer.Customer,
	tableCustomer.CustomerName,
	Brand.Brand,
	Data.Qty,
	App2.Milliliters
ORDER BY
	tableCustomer.CustomerName

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-11-02 : 14:24:55

If you need more help then we need table structure(s) and sample data and wanted result.

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources