Displaying SELECT Query Results - from Same Table - Side by Side

I've got an SQL query that I just can't get to display the way I want it to. Basically, we've got a table full of transactions and I'd like to get a sum (of a particular type of transaction ) purchased from February of this year until now. Here's the query for that:

select sum(amount) as "2011" from transactions where transaction_type = 'registration' and date_entered > '2011-02-01' and date_entered < GetDate()

Now, I'd also like to see a sum of those same transactions but for the previous year:

select sum(amount) as "2010" from transactions where transaction_type = 'registration' and date_entered > '2010-02-01' and date_entered < DateAdd(yy, -1, GetDate())

What I can't seem to figure out is how to get these sums side-by-side. I've tried UNION all but those display in separate rows, not columns.

select sum(amount) as "2011" from transactions where transaction_type = 'registration' and date_entered > '2011-02-01' and date_entered < GetDate() UNION all select sum(amount) as "2010" from transactions where transaction_type = 'registration' and date_entered > '2010-02-01' and date_entered < DateAdd(yy, -1, GetDate())

I've also read here on Stack Overflow that PIVOT might be an option but I've yet to see an example that I could manipulate/tweak for the queries above.

Any suggestions for how I can get this data side-by-side? I'm sure I'm overlooking something simple.

Many thanks!

-------------Problems Reply------------

You want a "pivot", which is essentially a calculation of the form sum(test * amount).
Here's how to do a pivot in your case:

sum(case when date_entered between '2011-02-01' and < GetDate() then amount else 0 end) as "2011",
sum(case when date_entered between '2010-02-01' and DateAdd(yy, -1, GetDate() then amount else 0 end) as "2010"
from transactions
where transaction_type = 'registration';

The quick and UGLY solution is this:

select sum(amount) as "2011" from transactions
where transaction_type = 'registration'
and date_entered > '2011-02-01'
and date_entered < GetDate() ) as '2011',
(select sum(amount) as "2010" from transactions
where transaction_type = 'registration'
and date_entered > '2010-02-01'
and date_entered < DateAdd(yy, -1, GetDate())) as '2010'

You can use this for a one-off query but is certainly something I want add to a production system.

For a good example on PIVOT check this one: http://rajaramtechtalk.wordpress.com/2008/05/13/how-to-use-pivot-in-sql-2005/

Your problem is that you are starting from February, so using DATEPART year wont work for you and you may have to use month and them do some work with the results.

Category:sql Views:0 Time:2011-08-19

Related post

  • SQLite INSERT SELECT Query Results into Existing Table? 2011-09-12

    I've not used SQLite before but I'm muddling my way through it. I need to Insert the results from a select query into a existing table but even after some lengthy web reading I'm still failing to complete this simple task. I'm using SQLite Studio to

  • How to save select query results within temporary table? 2010-12-08

    I need to save select query output into temporary table. Then I need to make another select query against this temporary table. Does anybody know how to do it? I need to make this on SQL Server. --------------Solutions------------- You can also do th

  • Displaying mysql query result set as a table 2012-01-03

    I want to display a query result set as a table on my webpage. The query is not causing any errors, but the script is not producing any output. I have one table in database (players). This table has 3 rows (id, firstname, lastname). Script source cod

  • Pan and zoom to display the query results by using Fusion Table Layer 2012-02-05

    I am beginner in Fusion Table Layer. I would like to display the query results as here (pan and zoom): https://developers.google.com/fusiontables/docs/samples/search_and_zoom ,but I can't do this for my function with AND clause: function changeMap()

  • Passing DropDown selected value along with other selected query result set 2011-01-20

    I have a form "test.cfm" It passes values to an action page "testAction.cfm"! test.cfm does the following:- A drop down box “fruitsList”. A query “qryFruits” that pulls data and then displays the query-result set “Add” button for every query-result s

  • Display filtered query results on a form based on information displayed on form 2013-01-19

    I have created a form based on all of the data in a table called Application I want to display on the bottom of the form a list box that displays the results of a query. The query contains more information that I need and I want the results to be fil

  • how can i add select query result into dataset later again select query run 2009-04-08

    i want to add select query result into dataset, so i can write new query to run on it to get net dataset but how? Original query: MyDATASET=( select x, y,z from table1 union all select k,l,m from table2 ) i wan to this select * from this.MyDATASET --

  • How Do I remove query results from a table? 2014-06-09

    I have two tables. Table 1 is a Master List of Names and Vehicle IDs. Table 2 is a list of Names and Vehicle IDs generated daily when each vehicle passes a sensor. Not all vehicles pass the sensor in a given time period. I created a Query that compar

  • how read two records at once from sql select query results? 2011-03-05

    i want to read two records each time from select query results in PHP ? --------------Solutions------------- like this: while($row1 = mysql_fetch_array($result)) { $row2 = mysql_fetch_array($result); // Do something } EDIT: Then it would be like this

  • Display query result in a table 2013-03-16

    I have a MYSQL query with over 50 return results. Now I need to display that results in a table with 3 rows and 3 td for each row. Something like this : <table> <tr> <td>Content</td> <td>Content</td> <td>Cont

  • How to keep a table that contains select query results 2011-02-24

    Hi I have 2 select queries that are returned to two seperate tables. While i am processing one of them inside a function i need the other query result to be kept inside a global parameter. How do i save the query result so it wont be overwritten when

  • Codeigniter: Display RAW query results 2011-08-30

    Is there a way to get the result of a query that was created with Active Record without using stuff like print_r() ? I know about the Profiler in Codeigniter but I need something that also displays the output of the query, not only the query itself (

  • Display SQL query result on VB form 2011-10-17

    I am facing a problem displaying the result of my "select *" query on the form . Here is my code: Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click myconnection = New SqlConnection("server=PAR

  • Adding Row Numbers To a SELECT Query Result in SQL Server Without use Row_Number() function 2009-10-29

    i need Add Row Numbers To a SELECT Query without using Row_Number() function. and without using user defined functions or stored procedures. Select (obtain the row number) as [Row], field1, field2, fieldn from aTable UPDATE i am using SAP B1 DIAPI, t

  • SQL Server PRINT SELECT (Print a select query result)? 2009-12-22

    I am trying to print a selected value, is this possible? Example: PRINT SELECT SUM(Amount) FROM Expense --------------Solutions------------- You know, there might be an easier way but the first thing that pops to mind is: Declare @SumVal int; Select

  • Display PHP Query Array in the table 2010-05-20

    Hi all I would like to display my Query from Mysql on the table. However, my data is like this: Array([0]=>data1, [1]=>data2, [2]=>data3,[3]=>data4,[4]=>pic1,[5]=>pic2,[6]=>pic3,[7]=>pic4); I want to display my table as |data1

  • MySQL: Include COUNT of SELECT Query Results as a Column (Without Grouping) 2010-09-29

    I have a simple report sending framework that basically does the following things: It performs a SELECT query, it makes some text-formatted tables based on the results, it sends an e-mail, and it performs an UPDATE query. This system is a generalizat

  • MYSQL select query based on another tables entries 2010-11-14

    I have stumped on this as I am a total beginner in MySql. Here is a the basic of how the two tables are formed Table 1 id,product_id, product_name Table 2 id,product_id,active Now i know how to do a select statement to query the results from one tabl

  • Databinding LINQ query results (with join tables) to DataGrid 2011-02-15

    In my WPF 4 desktop-based application I have a LINQ query that makes a join with two tables. Here is code of function that returns results of LINQ query: public IList GetTableData() { IList dataList = (from dWorker in App.glidusContext.tbl_workers jo

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.141 (s). 11 q(s)