Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 305
  • Last Modified:

SQL query, table's field to field's value

What I'm going to ask a bit tricky.

I have 2 tables, m_status and m_trans

m_status
----------
code     status
1          Car
2          Bus
3          Lorry



m_trans
---------
id        car        bus           lorry        train
1         10         5              3             23
2         4           8              1             3
3         7           12            23            7
4         5           2              3              8

My question is how do I sum the value in m_trans table where m_trans table fields name equivalent to m_status.status values (Car, Bus and Lorry)?
0
showbix
Asked:
showbix
  • 4
  • 2
1 Solution
 
jdlambert1Commented:
I'm not certain I understand the results you want, but I'm guessing you mean this:

SELECT TotalCar, TotalBus, TotalLorry, TotalTrain FROM
(SELECT Sum(Car) TotalCar FROM m_trans) as c,
(SELECT Sum(Bus) TotalBus FROM m_trans) as b,
(SELECT Sum(Lorry) TotalLorry FROM m_trans) as l,
(SELECT Sum(Train) TotalTrain FROM m_trans) as t
0
 
elchukgCommented:
Create and use the following procedure to sum the value in m_trans table where m_trans table fields name equivalent to m_status.status values

CREATE   PROCEDURE GetStatusTotals AS
DECLARE tTotals_cursor CURSOR
FOR
   SELECT status
   FROM m_status
OPEN tTotals_cursor
DECLARE @statusname sysname
--SET @tablename = 'authors'
FETCH NEXT FROM tTotals_cursor INTO @statusname
WHILE (@@FETCH_STATUS <> -1)
BEGIN
   IF (@@FETCH_STATUS <> -2)
   BEGIN  
      IF @statusname = 'Car'
      BEGIN
      EXEC ('SELECT sum(car) As Total_Car  FROM m_trans '
            )
      PRINT ' '
      END
      IF @statusname = 'Bus'
      BEGIN
      EXEC ('SELECT sum(bus) As Total_Bus  FROM m_trans '
            )
      PRINT ' '
      END      IF @statusname = 'Lorry'
      BEGIN
      EXEC ('SELECT sum(lorry) As Total_Lorry  FROM m_trans '
            )
      PRINT ' '
      END      IF @statusname = 'Train'
      BEGIN
      EXEC ('SELECT sum(train) As Total_Train  FROM m_trans '
            )
      PRINT ' '
      END   END
   FETCH NEXT FROM tTotals_cursor INTO @statusname
END
CLOSE tTotals_cursor
DEALLOCATE tTotals_cursor

Hope this is what you were looking for.

Glen Elchuk
0
 
jdlambert1Commented:
Boy, I don't know if I could get any goofier than with my first suggestion. If you like those results, all you need is:

SELECT Sum(Car) TotalCar, Sum(Bus) TotalBus, Sum(Lorry) TotalLorry, Sum(Train) TotalTrain FROM m_trans
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
elchukgCommented:
Here is a shorter version of my procedure

CREATE    PROCEDURE GetStatusTotals AS
DECLARE tTotals_cursor CURSOR
FOR
   SELECT status
   FROM m_status
OPEN tTotals_cursor
DECLARE @statusname sysname
FETCH NEXT FROM tTotals_cursor INTO @statusname
WHILE (@@FETCH_STATUS <> -1)
BEGIN
   IF (@@FETCH_STATUS <> -2)
   BEGIN  

      EXEC ('SELECT sum(' + @statusname + ') As Total_' + @statusname + '  FROM m_trans '
            )
      PRINT ' '
  END
   FETCH NEXT FROM tTotals_cursor INTO @statusname
END
CLOSE tTotals_cursor
DEALLOCATE tTotals_cursor


I hope this helps,

Glen Elchuk

0
 
elchukgCommented:
If you want the results on one line as one query use the following procedure,

CREATE PROCEDURE GetStatusTotals AS
DECLARE tTotals_cursor CURSOR
FOR
   SELECT status
   FROM m_status
OPEN tTotals_cursor
DECLARE @statusname sysname,
        @SumValues sysname
Set @SumValues = 'sum(ID)AS Total'
FETCH NEXT FROM tTotals_cursor INTO @statusname
Set @SumValues = 'SUM(' + RTRIM(@statusname) + ') As Total_' + RTRIM(@statusname)
FETCH NEXT FROM tTotals_cursor INTO @statusname
WHILE (@@FETCH_STATUS <> -1)
BEGIN
   IF (@@FETCH_STATUS <> -2)
   BEGIN  

      Set @SumValues = @SumValues + ', sum(' + RTRIM(@statusname) + ') As Total_' + RTRIM(@statusname)
           
     
  END
   FETCH NEXT FROM tTotals_cursor INTO @statusname
END
EXEC ('SELECT ' + @SumValues  + '  FROM m_trans ')
   
CLOSE tTotals_cursor
DEALLOCATE tTotals_cursor
0
 
showbixAuthor Commented:
Hi Elchuk.
What is FETCH_STATUS? What does the -1 and -2 means?
By the way, your solution works great.
Thanks.
0
 
elchukgCommented:
Hi showbix,

@@FETCH_STATUS
Returns the status of the last cursor FETCH statement issued against any cursor currently opened by the connection.

Return value Description
0 FETCH statement was successful.
-1 FETCH statement failed or the row was beyond the result set.
-2 Row fetched is missing.

Glad I could heip,


Glen Elchuk

0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now