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)
 Total In bottom Row

Author  Topic 

direrayne
Starting Member

30 Posts
Posted - 2010-09-18 : 15:10:39
I am trying to total in bottom row.
I am using the code bellow but i need to add the code to total as the example bellow.
Someone please help.


SELECT 
DISTINCT
isnull(v.VendorID,'NOT DEFINED') as VendorID,
isnull(h.ShipClass, 'NOT DEFINED' ) as ShipClass,
isnull(h.Ship, 'NOT DEFINED') as [SourceofSales],
isnull(h.MediaCompany, 'NOT DEFINED') as MediaCompany, 
a.[Time] AS [Date of Sales], 
 isnull(S.Name, 'NOT DEFINED') AS [Store Code],
 r.Name as Salesperson, 
 t.TransactionNumber,
--r.Number + '-' + r.Name as SalesRep,
 i.ItemLookupCode as [Item Number], 
cast(i.ExtendedDescription as varchar(90)) as [Item Description], 
 t.Quantity as [Quantity Sold], 
 t.Price as [Unit Price],
 i.Price as [List Price],
t.ID,
(t.Quantity*t.Price) AS [Extented Price]
FROM (((((([10.1.1.18\RMS20].ICLHQ.dbo.TransactionEntry t 
LEFT JOIN [10.1.1.18\RMS20].ICLHQ.dbo.Item i ON t.ItemID = i.ID) 
LEFT JOIN [10.1.1.18\RMS20].ICLHQ.dbo.SalesRep r ON (t.StoreID = r.StoreID)
AND (t.SalesRepID = r.ID)) 
LEFT JOIN [10.1.1.18\RMS20].ICLHQ.dbo.ShipTrans p ON (t.StoreID = p.StoreID) 
AND (t.TransactionNumber = p.TransactionID)) 
LEFT JOIN [10.1.1.18\RMS20].ICLHQ.dbo.ShipnSchedule h ON 
p.ShipID = h.ShipID) 
LEFT JOIN [10.1.1.18\RMS20].ICLHQ.dbo.Store s ON t.StoreID = s.ID)
LEFT JOIN [10.1.1.18\RMS20].ICLHQ.dbo.[Transaction] a ON (t.StoreID = a.StoreID)
AND (t.TransactionNumber = a.TransactionNumber)) 
LEFT OUTER JOIN (select 
      A.ITEMNMBR
      ,PM00200.VENDORID AS VENDORID
from IV00103 A inner join
(select ITEMNMBR, count(*) as Cuenta
from IV00103 B
group by ITEMNMBR having count(*) = 1) NOREP on (A.ITEMNMBR = NOREP.ITEMNMBR)
inner join PM00200 on (A.VENDORID = PM00200.VENDORID)
UNION ALL
select 
      A.ITEMNMBR
      ,'VENDOR ERROR' AS VENDORID
from IV00103 A inner join
(select ITEMNMBR, count(*) as Cuenta
from IV00103 B
group by ITEMNMBR having count(*) > 1) NOREP on (A.ITEMNMBR = NOREP.ITEMNMBR)
GROUP BY A.ITEMNMBR) v ON i.ItemLookupCode = v.ITEMNMBR
WHERE v.VendorID = 'hof'
-- AND (@SourceofSale = 'ALL' OR h.ShipClass=@SourceofSale)
AND (a.[time]>='2010-08-01' AND a.[time]<='2010-08-30')
-- AND (S.name in (@Store))
-- AND (@MediaCompany = 'ALL' OR h.MediaCompany=@MediaCompany)
order by       
      [VendorID]
      ,[SourceofSales]
      ,[Store Code]
      ,[Salesperson]
      ,[MediaCompany]
      ,[Date of Sales]
      ,[TransactionNumber]
      ,[Item Number]
      ,[Item Description]
      ,[Quantity Sold]
      ,[Unit Price]
      ,[List Price]
      ,[Extented Price]


the Result gives me data columns "unit price", "list price", "quantity sold", and "extended price"
I would like to total in the row directly bellow it.

Unit Price
100
200
300

Total 600

webfred
Master Smack Fu Yak Hacker

8781 Posts
Posted - 2010-09-18 : 15:15:10
That has to be done using a reporting tool.
SQL was not made for that.

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

direrayne
Starting Member

30 Posts
Posted - 2010-09-18 : 15:16:42
quote:
Originally posted by webfred

That has to be done using a reporting tool.
SQL was not made for that.

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


i have seen this done my friend
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts
Posted - 2010-09-18 : 15:39:34
Yes I know. I havn't said it is not possible.
If someone is willing to give the solution for that.
Here is the more readable code:
SELECT DISTINCT Isnull(v.vendorid, 'NOT DEFINED')          AS vendorid, 
                Isnull(h.shipclass, 'NOT DEFINED')         AS shipclass, 
                Isnull(h.ship, 'NOT DEFINED')              AS [SourceofSales], 
                Isnull(h.mediacompany, 'NOT DEFINED')      AS mediacompany, 
                a.[Time]                                   AS [Date of Sales], 
                Isnull(s.name, 'NOT DEFINED')              AS [Store Code], 
                r.name                                     AS salesperson, 
                t.transactionnumber, 
                --r.Number + '-' + r.Name as SalesRep, 
                i.itemlookupcode                           AS [Item Number], 
                CAST(i.extendeddescription AS VARCHAR(90)) AS [Item Description], 
                t.quantity                                 AS [Quantity Sold], 
                t.price                                    AS [Unit Price], 
                i.price                                    AS [List Price], 
                t.id, 
                ( t.quantity * t.price )                   AS [Extented Price] 
FROM   (((((([10.1.1.18\RMS20].iclhq.dbo.transactionentry t 
             LEFT JOIN [10.1.1.18\RMS20].iclhq.dbo.item i 
               ON t.itemid = i.id) 
            LEFT JOIN [10.1.1.18\RMS20].iclhq.dbo.salesrep r 
              ON ( t.storeid = r.storeid ) 
                 AND ( t.salesrepid = r.id )) 
           LEFT JOIN [10.1.1.18\RMS20].iclhq.dbo.shiptrans p 
             ON ( t.storeid = p.storeid ) 
                AND ( t.transactionnumber = p.transactionid )) 
          LEFT JOIN [10.1.1.18\RMS20].iclhq.dbo.shipnschedule h 
            ON p.shipid = h.shipid) 
         LEFT JOIN [10.1.1.18\RMS20].iclhq.dbo.store s 
           ON t.storeid = s.id) 
        LEFT JOIN [10.1.1.18\RMS20].iclhq.dbo.[Transaction] a 
          ON ( t.storeid = a.storeid ) 
             AND ( t.transactionnumber = a.transactionnumber )) 
       LEFT OUTER JOIN (SELECT a.itemnmbr, 
                               pm00200.vendorid AS vendorid 
                        FROM   iv00103 a 
                               INNER JOIN (SELECT itemnmbr, 
                                                  COUNT(*) AS cuenta 
                                           FROM   iv00103 b 
                                           GROUP  BY itemnmbr 
                                           HAVING COUNT(*) = 1) norep 
                                 ON ( a.itemnmbr = norep.itemnmbr ) 
                               INNER JOIN pm00200 
                                 ON ( a.vendorid = pm00200.vendorid ) 
                        UNION ALL 
                        SELECT a.itemnmbr, 
                               'VENDOR ERROR' AS vendorid 
                        FROM   iv00103 a 
                               INNER JOIN (SELECT itemnmbr, 
                                                  COUNT(*) AS cuenta 
                                           FROM   iv00103 b 
                                           GROUP  BY itemnmbr 
                                           HAVING COUNT(*) > 1) norep 
                                 ON ( a.itemnmbr = norep.itemnmbr ) 
                        GROUP  BY a.itemnmbr) v 
         ON i.itemlookupcode = v.itemnmbr 
WHERE  v.vendorid = 'hof' 
       -- AND (@SourceofSale = 'ALL' OR h.ShipClass=@SourceofSale) 
       AND ( a.[time] >= '2010-08-01' 
             AND a.[time] <= '2010-08-30' ) 
-- AND (S.name in (@Store)) 
-- AND (@MediaCompany = 'ALL' OR h.MediaCompany=@MediaCompany) 
ORDER  BY [VendorID], 
          [SourceofSales], 
          [Store Code], 
          [Salesperson], 
          [MediaCompany], 
          [Date of Sales], 
          [TransactionNumber], 
          [Item Number], 
          [Item Description], 
          [Quantity Sold], 
          [Unit Price], 
          [List Price], 
          [Extented Price]


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

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts
Posted - 2010-09-18 : 16:54:06
Webfred is correct, Adding a row total should be handled outside this. In order to do what yo want though you basically just run the query twice and group the second one. as in:


SELECT DISTINCT 1 as SORT,
Isnull(v.vendorid, 'NOT DEFINED')          AS vendorid, 
                Isnull(h.shipclass, 'NOT DEFINED')         AS shipclass, 
                Isnull(h.ship, 'NOT DEFINED')              AS [SourceofSales], 
                Isnull(h.mediacompany, 'NOT DEFINED')      AS mediacompany, 
                a.[Time]                                   AS [Date of Sales], 
                Isnull(s.name, 'NOT DEFINED')              AS [Store Code], 
                r.name                                     AS salesperson, 
                t.transactionnumber, 
                --r.Number + '-' + r.Name as SalesRep, 
                i.itemlookupcode                           AS [Item Number], 
                CAST(i.extendeddescription AS VARCHAR(90)) AS [Item Description], 
                t.quantity                                 AS [Quantity Sold], 
                t.price                                    AS [Unit Price], 
                i.price                                    AS [List Price], 
                t.id, 
                ( t.quantity * t.price )                   AS [Extented Price] 
FROM   (((((([10.1.1.18\RMS20].iclhq.dbo.transactionentry t 
             LEFT JOIN [10.1.1.18\RMS20].iclhq.dbo.item i 
               ON t.itemid = i.id) 
            LEFT JOIN [10.1.1.18\RMS20].iclhq.dbo.salesrep r 
              ON ( t.storeid = r.storeid ) 
                 AND ( t.salesrepid = r.id )) 
           LEFT JOIN [10.1.1.18\RMS20].iclhq.dbo.shiptrans p 
             ON ( t.storeid = p.storeid ) 
                AND ( t.transactionnumber = p.transactionid )) 
          LEFT JOIN [10.1.1.18\RMS20].iclhq.dbo.shipnschedule h 
            ON p.shipid = h.shipid) 
         LEFT JOIN [10.1.1.18\RMS20].iclhq.dbo.store s 
           ON t.storeid = s.id) 
        LEFT JOIN [10.1.1.18\RMS20].iclhq.dbo.[Transaction] a 
          ON ( t.storeid = a.storeid ) 
             AND ( t.transactionnumber = a.transactionnumber )) 
       LEFT OUTER JOIN (SELECT a.itemnmbr, 
                               pm00200.vendorid AS vendorid 
                        FROM   iv00103 a 
                               INNER JOIN (SELECT itemnmbr, 
                                                  COUNT(*) AS cuenta 
                                           FROM   iv00103 b 
                                           GROUP  BY itemnmbr 
                                           HAVING COUNT(*) = 1) norep 
                                 ON ( a.itemnmbr = norep.itemnmbr ) 
                               INNER JOIN pm00200 
                                 ON ( a.vendorid = pm00200.vendorid ) 
                        UNION ALL 
                        SELECT a.itemnmbr, 
                               'VENDOR ERROR' AS vendorid 
                        FROM   iv00103 a 
                               INNER JOIN (SELECT itemnmbr, 
                                                  COUNT(*) AS cuenta 
                                           FROM   iv00103 b 
                                           GROUP  BY itemnmbr 
                                           HAVING COUNT(*) > 1) norep 
                                 ON ( a.itemnmbr = norep.itemnmbr ) 
                        GROUP  BY a.itemnmbr) v 
         ON i.itemlookupcode = v.itemnmbr 
WHERE  v.vendorid = 'hof' 
       -- AND (@SourceofSale = 'ALL' OR h.ShipClass=@SourceofSale) 
       AND ( a.[time] >= '2010-08-01' 
             AND a.[time] <= '2010-08-30' ) 
-- AND (S.name in (@Store)) 
-- AND (@MediaCompany = 'ALL' OR h.MediaCompany=@MediaCompany) 
union all
Select SORT,null,null,null,null,null,null,null,null,null,null,sum([Quantity Sold]),sum([Unit price]),sum([List Price]), sum([Extended Price])
from
(
SELECT DISTINCT 2 as SORT
Isnull(v.vendorid, 'NOT DEFINED')          AS vendorid, 
                Isnull(h.shipclass, 'NOT DEFINED')         AS shipclass, 
                Isnull(h.ship, 'NOT DEFINED')              AS [SourceofSales], 
                Isnull(h.mediacompany, 'NOT DEFINED')      AS mediacompany, 
                a.[Time]                                   AS [Date of Sales], 
                Isnull(s.name, 'NOT DEFINED')              AS [Store Code], 
                r.name                                     AS salesperson, 
                t.transactionnumber, 
                --r.Number + '-' + r.Name as SalesRep, 
                i.itemlookupcode                           AS [Item Number], 
                CAST(i.extendeddescription AS VARCHAR(90)) AS [Item Description], 
                t.quantity                                 AS [Quantity Sold], 
                t.price                                    AS [Unit Price], 
                i.price                                    AS [List Price], 
                t.id, 
                ( t.quantity * t.price )                   AS [Extented Price] 
FROM   (((((([10.1.1.18\RMS20].iclhq.dbo.transactionentry t 
             LEFT JOIN [10.1.1.18\RMS20].iclhq.dbo.item i 
               ON t.itemid = i.id) 
            LEFT JOIN [10.1.1.18\RMS20].iclhq.dbo.salesrep r 
              ON ( t.storeid = r.storeid ) 
                 AND ( t.salesrepid = r.id )) 
           LEFT JOIN [10.1.1.18\RMS20].iclhq.dbo.shiptrans p 
             ON ( t.storeid = p.storeid ) 
                AND ( t.transactionnumber = p.transactionid )) 
          LEFT JOIN [10.1.1.18\RMS20].iclhq.dbo.shipnschedule h 
            ON p.shipid = h.shipid) 
         LEFT JOIN [10.1.1.18\RMS20].iclhq.dbo.store s 
           ON t.storeid = s.id) 
        LEFT JOIN [10.1.1.18\RMS20].iclhq.dbo.[Transaction] a 
          ON ( t.storeid = a.storeid ) 
             AND ( t.transactionnumber = a.transactionnumber )) 
       LEFT OUTER JOIN (SELECT a.itemnmbr, 
                               pm00200.vendorid AS vendorid 
                        FROM   iv00103 a 
                               INNER JOIN (SELECT itemnmbr, 
                                                  COUNT(*) AS cuenta 
                                           FROM   iv00103 b 
                                           GROUP  BY itemnmbr 
                                           HAVING COUNT(*) = 1) norep 
                                 ON ( a.itemnmbr = norep.itemnmbr ) 
                               INNER JOIN pm00200 
                                 ON ( a.vendorid = pm00200.vendorid ) 
                        UNION ALL 
                        SELECT a.itemnmbr, 
                               'VENDOR ERROR' AS vendorid 
                        FROM   iv00103 a 
                               INNER JOIN (SELECT itemnmbr, 
                                                  COUNT(*) AS cuenta 
                                           FROM   iv00103 b 
                                           GROUP  BY itemnmbr 
                                           HAVING COUNT(*) > 1) norep 
                                 ON ( a.itemnmbr = norep.itemnmbr ) 
                        GROUP  BY a.itemnmbr) v 
         ON i.itemlookupcode = v.itemnmbr 
WHERE  v.vendorid = 'hof' 
       -- AND (@SourceofSale = 'ALL' OR h.ShipClass=@SourceofSale) 
       AND ( a.[time] >= '2010-08-01' 
             AND a.[time] <= '2010-08-30' ) 
-- AND (S.name in (@Store)) 
-- AND (@MediaCompany = 'ALL' OR h.MediaCompany=@MediaCompany) 
) 
group by SORT
ORDER  BY [SORT],
[VendorID], 
          [SourceofSales], 
          [Store Code], 
          [Salesperson], 
          [MediaCompany], 
          [Date of Sales], 
          [TransactionNumber], 
          [Item Number], 
          [Item Description], 
          [Quantity Sold], 
          [Unit Price], 
          [List Price], 
          [Extented Price]




Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-09-19 : 11:40:17
can i ask reason for asking this in SQL? whats the front end tool you're using?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

direrayne
Starting Member

30 Posts
Posted - 2010-09-20 : 09:49:28
quote:
Originally posted by visakh16

can i ask reason for asking this in SQL? whats the front end tool you're using?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/




I need the report to be processed using SSIS and then email from DB in excel format to the bosses but i am missing the total's.
Go to Top of Page

kunal.mehta
Yak Posting Veteran

83 Posts
Posted - 2010-09-20 : 10:30:45
DECLARE @T TABLE (item varchar(10),Unit_Price int)

INSERT @t VALUES('item1',100)
INSERT @t VALUES('item2',200)
INSERT @t VALUES('item3',300)


SELECT isnull(item,'total') item,SUM(unit_price) FROM @T
group by item WITH ROLLUP
Go to Top of Page

direrayne
Starting Member

30 Posts
Posted - 2010-09-20 : 11:17:55
quote:
Originally posted by kunal.mehta

DECLARE @T TABLE (item varchar(10),Unit_Price int)

INSERT @t VALUES('item1',100)
INSERT @t VALUES('item2',200)
INSERT @t VALUES('item3',300)


SELECT isnull(item,'total') item,SUM(unit_price) FROM @T
group by item WITH ROLLUP


I think that might work, but can some one give me an example in code?
i can do this with a table but from query i am a bit lost.
Go to Top of Page

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts
Posted - 2010-09-20 : 22:31:19
quote:
Originally posted by kunal.mehta

DECLARE @T TABLE (item varchar(10),Unit_Price int)

INSERT @t VALUES('item1',100)
INSERT @t VALUES('item2',200)
INSERT @t VALUES('item3',300)


SELECT isnull(item,'total') item,SUM(unit_price) FROM @T
group by item WITH ROLLUP


I never even knew of this function. This is a very good thing to know, thanks for posting this info.

Here's more info if anyone else is interested.

http://odetocode.com/Articles/85.aspx

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site