CODEWITHSUNDEEP
sql-server-2005select
Shoukat
Shoukat

Reputation: 11

Muti Row Select sub Query

I have 3 queries and they work fine. There Queries are: 

SELECT SUM(SALES)as NETSALES FROM Sales WHERE DOCREF='1'GROUP BY GEOCODE

above query results :

NETSALES 
1
2 
3

 SELECT SUM(SALES)as FRESHRETURNS FROM Sales WHERE DOCREF='2'GROUP BY GEOCODE

above query results :

FRESHRETURNS
1
2
3

 SELECT SUM(SALES)as SALESRETURNS FROM Sales WHERE DOCREF='3'GROUP BY GEOCODE

above query results :

SALESRETURNS
1
2
3

is there any way to combine these statements to get the result as

     NETSALES   |     FRESHRETURNS      |  SALESRETURNS
 1------1-------|-----------1-----------|--------1--------
 2------2-------|-----------2-----------|--------2--------
 3------3-------|-----------2-----------|--------3--------

Upvotes: 0

Views: 134

Answers (4)

Jesse
Jesse

Reputation: 8393

Depending on which database product you are using, you may need to tweak this a bit, but something like this should work for you:

SELECT GEOCODE, SUM(NETSALES), SUM(FRESHRETURNS), SUM(SALESRETURNS)
FROM
(
    SELECT GEOCODE, SUM(SALES)as NETSALES, 0 AS FRESHRETURNS, 0 AS SALESRETURNS FROM Sales WHERE DOCREF='1'GROUP BY GEOCODE
    UNION ALL
    SELECT GEOCODE, 0 AS NETSALES, SUM(SALES)as FRESHRETURNS, 0 AS SALESRETURNS FROM Sales WHERE DOCREF='2'GROUP BY GEOCODE
    UNION ALL
    SELECT GEOCODE, 0 AS NETSALES, 0 AS FRESHRETURNS, SUM(SALES)as SALESRETURNS FROM Sales WHERE DOCREF='3'GROUP BY GEOCODE
) AS salesData
GROUP BY GEOCODE

Upvotes: 0

JonH
JonH

Reputation: 33143

You didn't mention if you are using MS Sql Server or Oracle, I am assuming MS :)

Make use of CASE and you can basically build a matrix with the result you want:

CREATE TABLE #t
 (
  Sale int,
  DocRef varchar(1),
  GeoCode varchar(1)
 )

INSERT INTO #t(Sale, DocRef,GeoCode) VALUES(100, '1', 'A')
INSERT INTO #t(Sale, DocRef,GeoCode) VALUES(120, '1', 'A')
INSERT INTO #t(Sale, DocRef,GeoCode) VALUES(110, '2', 'B')
INSERT INTO #t(Sale, DocRef,GeoCode) VALUES(120, '2', 'B')
INSERT INTO #t(Sale, DocRef,GeoCode) VALUES(100, '3', 'C')
INSERT INTO #t(Sale, DocRef,GeoCode) VALUES(100, '3', 'C')
INSERT INTO #t(Sale, DocRef,GeoCode) VALUES(100, '3', 'A')


SELECT 
      CASE WHEN DocRef='1' THEN SUM(Sale) ELSE 0 END as NETSALES,       
      CASE WHEN DocRef='2' THEN SUM(Sale) ELSE 0 END AS FRESHRETURNS,       
      CASE WHEN DocRef='3' THEN SUM(Sale) ELSE 0 END AS SALESRETURNS
FROM       
    #t 
GROUP BY       
    GeoCode,
    DocRef

DROP TABLE #t

Upvotes: 3

Danny
Danny

Reputation: 7518

Probably terribly inefficient but works for me on Oracle

SELECT (SELECT SUM(SALES) FROM Sales WHERE DOCREF='1'GROUP BY GEOCODE) NETSALES,
   (SELECT SUM(SALES) FROM Sales WHERE DOCREF='2'GROUP BY GEOCODE) FRESHRETURNS ,
   (SELECT SUM(SALES) FROM Sales WHERE DOCREF='3'GROUP BY GEOCODE) SALESRETURNS FROM DUAL

Upvotes: 0

Icarus
Icarus

Reputation: 63956

I think this works but I haven't tested. I'm adding a fake ID column, giving it value 'x' and joining the 3 result sets using this new ID:

select t.NETSALES, ta.FRESHRETURNS , tb.SALESRETURNS from 
(SELECT 'x' as ID, SUM(SALES)as NETSALES FROM Sales WHERE DOCREF='1'GROUP BY GEOCODE,ID ) as t

inner join 
(SELECT 'x' as ID, SUM(SALES)as FRESHRETURNS FROM Sales WHERE DOCREF='2'GROUP BY GEOCODE,ID) ta on ta.ID=t.ID

inner join 
(SELECT 'x' as ID SUM(SALES)as SALESRETURNS FROM Sales WHERE DOCREF='3'GROUP BY GEOCODE,ID ) tb on tb.ID=t.ID

Upvotes: 0

Related Questions