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)
 Grouby giving dupes

Author  Topic 

RammohanB
Starting Member

9 Posts

Posted - 2012-11-30 : 14:29:53
select SUM(A.price),B.bid,C.name from prices A
JOIN sal B ON B.id=A.id
JOIN persons C ON C.name= B.name
WHERE a.date = '2012-11-29'
group by bid,name
---
i am using above query but i am wrong values, i am getting sum(price) for the date for one id in one row . how can i get instead of two three rows to single row...i have multiple names having different ids but i need all ids prices in single row using name


Rammohan

shilpash
Posting Yak Master

103 Posts

Posted - 2012-11-30 : 14:40:39
SELECT SUM(A.price)
,B.bid
FROM prices A
JOIN sal B
ON B.id = A.id
WHERE a.date = '2012-11-29'
GROUP BY b.bid
Go to Top of Page

RammohanB
Starting Member

9 Posts

Posted - 2012-11-30 : 14:43:31
It worked thank you very much

Rammohan
Go to Top of Page

RammohanB
Starting Member

9 Posts

Posted - 2012-11-30 : 14:46:17
And also i want name too in my select list which is common in persons and bid

Rammohan
Go to Top of Page

shilpash
Posting Yak Master

103 Posts

Posted - 2012-11-30 : 15:25:27
WITH PriceCte
AS (SELECT SUM(A.price) price
,B.id
FROM prices A
JOIN sal B
ON B.id = A.id
WHERE a.date = '2012-11-29'
GROUP BY b.bid
)
SELECT p.person
,p.id
,t.price
FROM persons p
LEFT JOIN PriceCte T
ON T.id = p.id
Go to Top of Page

shilpash
Posting Yak Master

103 Posts

Posted - 2012-11-30 : 15:30:21
WITH PriceCte
AS (SELECT SUM(A.price) price
,B.id
FROM prices A
JOIN sal B
ON B.id = A.id
GROUP BY b.bid
)
SELECT p.person
,p.id
,t.price
FROM persons p
LEFT JOIN PriceCte T
ON T.id = p.id
WHERE P.date = '2012-11-29'
Go to Top of Page
   

- Advertisement -