SQL Query

[GrimStoner]

Sample data :

Name | Description | Usage
-------------|--------------|--------
John Smith | SMS |
John Smith | Data usage | 10Mb
John Smith | Data usage | 5Mb
Jane Smith | MMS |
Jane Smith | Data usage | 44Mb

I need a sql query (that I'll run in Access 2007) to give me a SUM of the usage and a COUNT of the SMS's and MMS's for each [Name]. For example, the query will return the following data on the above sample data :

Name | SMS | MMS | TotalUsage
-------------|------|-------|-------------
John Smith | 1 | 0 | 15Mb
Jane Smith | 0 | 1 | 44Mb

I've tried :

SELECT Count(SELECT Description FROM transactions WHERE Description = "SMS"), (SELECT Sum(TotalUsage) FROM transactions WHERE Description = "Data usage"), (SELECT Count(SELECT Description FROM transactions WHERE Description = "SMS")) FROM transactions GROUP BY Name;

but the SELECT Sum(TotalUsage) FROM transactions WHERE Description = "Data usage" part gives me this error :

"At most one record can be returned by this subquery."

Edit : ASCII FTW!

[GrimStoner]

I've changed the subquery that gives the error to this :

SELECT Sum(SELECT Usage FROM transactions WHERE Description = "Data usage") AS SumOfTotalBytes
FROM transactions;

Still have the same error.

Edit : I've also tried

SELECT Name, (SELECT Sum(Usage) FROM transactions WHERE Description = "Data usage") AS SumOfTotalBytes
FROM transactions
GROUP BY Name;

but then the total of all the usage fields are returned with each name, i.e.

Jane Smith | 59Mb
John Smith | 59Mb

[Slasher]

Wait, WHAT?

So you have a SMS entry and every "Data usage" field under it should be counter till there is a change? Can you not change "Data usage" to read "SMS" or for whatever it is intended??

[GrimStoner]

nonono...

The table contains a list of all transactions that appeared for a certain name. In the sample table, John Smith sent 1 sms, and accessed a total of 15Mb of data. Jane Smith sent 1 MMS, and accessed a total of 44Mb, and that's what I want returned by the query.

[Slasher]

Ah, ok... Gimme a minute and I will get it sorted...

[GrimStoner]

Ok... this query

SELECT Name, Sum(Usage) AS SumOfTotalBytes
FROM Transactions
GROUP BY Name;

Correctly returns each name with the associated total usage.

But I need to merge that with the query that returns the sum of the SMS's (SELECT Count(Description) FROM transactions WHERE Description = "SMS"). EDIT : ... the sum of the SMS's PER [Name].

[GrimStoner]

This may be a stupid approach, but I've also tried creating two queries :

UsageByName:
SELECT Name, Sum(Usage) AS TotalUsageByName
FROM transactions
GROUP BY Name;

and

SMSSentByName:
SELECT Name, Count(Description) AS SMSSent
FROM transactions
GROUP BY Name, Description
HAVING (((Description)="SMS"));

Each returns the correct data per [Name]. Then I created a join query :

SELECT UsageByName.Name, SMSSentByName.SMSSent, UsageByName.TotalUsageByName
FROM UsageByName
INNER JOIN SMSSentByName ON UsageByName.Name = SMSSentByName.Name

Which correctly returns the usage and sms' sent per [Name] field. It does not however, return the the [Name] fields that had Data Usage, but that did not send an SMS (probably because those [Name]s are not in the SMSSentByName query).

This approach is cumbersome to say the least. I'll have to make another query for MMS's, and another for the other fields as well, and then Join them all together.

[Slasher]

Your problem is that if the name does not appear on the first list (e.g Just Jane is on query 1) then John's name does not exist if he is only on query two, due to matching issues...

[Slasher]

How about this Grim:

SELECT Data.Name, Sum(Data.Usage) AS TotalDataUsage, Description, Count(data.description) AS [AmountOfEntries]
FROM Data
GROUP BY Data.Name, data.description;

Just use your DBase name and field names, should be close to mine...

Returns the following:

Name TotalDataUsage Description AmountOfEntries
Jane Smith 44 Data Usage 1
Jane Smith 0 MMS 1
John Smith 15 Data Usage 2
John Smith 0 MMS 1
John Smith 0 SMS 2

Does not split the SMS and MMS ones, but does tell you how many entries there are per person...

Over and OUT...

[endev8003]

Grim, try this

Code: Select all

SELECT Name, SUM(SWITCH(Description = 'SMS', 1, Description = 'MMS', 0, Description='Data Usage', 0)) AS SMS,
SUM(SWITCH(Description = 'SMS', 0, Description = 'MMS', 1, Description='Data Usage', 0)) AS MMS,
SUM(SWITCH(Description = 'SMS', 0, Description = 'MMS', 0, Description='Data Usage', Usage)) AS TotalUsage
FROM Sample
GROUP BY Name

I tested it in Access 2003, so it should hopefully work in 2007 as well.

[GrimStoner]

Bloody marvellous mate!

If I wanna expand the query to include other columns, do I just modify the switch? I.e., if there were a column for Telkom calls called TelkomCalls (say that three times quickly) with a columb for the total cost of the call called (funnily enough) TotalCost, would it be

Code: Select all

SELECT Name, SUM(SWITCH(Description = 'SMS', 1, Description = 'MMS', 0, Description='Data Usage', 0, Description = 'TelkomCalls', 0)) AS SMS,
SUM(SWITCH(Description = 'SMS', 0, Description = 'MMS', 1, Description='Data Usage', 0, Description = 'TelkomCalls', 0)) AS MMS,
SUM(SWITCH(Description = 'SMS', 0, Description = 'MMS', 0, Description='Data Usage', Usage, Description = 'TelkomCalls', 0)) AS TotalUsage,
SUM(SWITCH(Description = 'SMS', 0, Description = 'MMS', 0, Description='Data Usage', 0, Description = 'TelkomCalls', TotalCost)) AS TotalCostOfTelkomCalls,

FROM Sample
GROUP BY Name

[GrimStoner]

K... the query works.

One annoyance though... Access has a limit on the arguments that can be passed to the SWITCH function. I had to split mine in two and then join them afterwards.

[endev8003]

Try this:

Code: Select all

SELECT Name, SUM(Nz(SWITCH(Description = 'SMS', 1), 0)) AS SMS, 
SUM(Nz(SWITCH(Description = 'MMS', 1), 0)) AS MMS, 
SUM(Nz(SWITCH(Description = 'Data Usage', Usage), 0)) AS TotalUsage,
SUM(Nz(SWITCH(Description = 'TelkomCalls', TotalCost), 0)) AS TotalCostOfTelkomCalls
FROM Sample
GROUP BY Name;

If the SWITCH function doesn't get a value in the list, it returns 'null'. The Nz function exchanges a 'null' for a predefined value, in this case 0.

[GrimStoner]

This is basically the same question as I had in my first post, just have to do it for MS SQL Server 2005 now...

I have the following query that runs in Access :

Code: Select all

SELECT 
      CellNumber, 
      SUM(Nz(SWITCH(Description='Data Usage',TotalBytes),0)) AS DataUsage,
      SUM(Nz(SWITCH(Description='Phone Calls',Cost),0)) AS CallsTotal,
      SUM(Nz(SWITCH(Description='SMS',1),0)) AS SMSCount
FROM 
      Transactions
GROUP BY 
      CellNumber

This basically gives me a total of all the Data Usage, total of the Phone Calls cost and a count of all the SMS's sent by a certain CellNumber.

I'm converting this query to a MS SQL 2005 query (or stored procedure). I've tried this in MS SQL 2005 :

Code: Select all

SELECT     
      CellNumber, 
      SUM(COALESCE (CASE Description WHEN 'Data Usage' THEN TotalBytes ELSE NULL)) AS DataUsage,
SUM(COALESCE (CASE Description WHEN 'Phone Calls' THEN Cost ELSE NULL)) AS CallsTotal,
SUM(COALESCE (CASE Description WHEN 'SMS' THEN 1 ELSE NULL)) AS SMSCount
FROM
      Transactions
GROUP BY 
      CellNumber

but it doesn't work... Keep getting a

Code: Select all

Error in list of function arguments: ')' not recognized.
Unable to parse query text.

error. Any suggestions?

Each row in table Transactions has the following columns :

CellNumber
Description
TotalBytes
Cost

[endev8003]

There are two errors in your sql statement that leaves similar error messages.

COALESCE uses a list statements or variables and then selects the first non-null that one of the statements give. You're only giving one argument. You don't actually need it. The sql statement works fine if you remove it.

The second error is that you need to put an END to each CASE. It should be like this: CASE Description WHEN 'Data Usage' THEN TotalBytes ELSE NULL END

After changing the sql statement, it should look like:

Code: Select all

SELECT     
      CellNumber,
      SUM(CASE Description WHEN 'Data Usage' THEN TotalBytes ELSE NULL END) AS DataUsage,
SUM(CASE Description WHEN 'Phone Calls' THEN Cost ELSE NULL END) AS CallsTotal,
SUM(CASE Description WHEN 'SMS' THEN 1 ELSE NULL END) AS SMSCount
FROM
      Transactions
GROUP BY
      CellNumber

Just one thing to note: If a number doesn't have one of items, like 0000000000 doesn't have any data usage, it returns a NULL. I'm not sure what you wish to return, so you will just have to change the query to suit yout needs.

[GrimStoner]

Just one thing to note: If a number doesn't have one of items, like 0000000000 doesn't have any data usage, it returns a NULL. I'm not sure what you wish to return, so you will just have to change the query to suit yout needs.

That's what I wanted to use COALESCE for... If the CASE returns a NULL, I'd like it to return 0.

I could prolly just do

Code: Select all

SUM(COALESCE(CASE function here END, 0))

then if the CASE returns NULL, SQL will take the next non-null value, which is 0?

[endev8003]

You can use ISNULL as well.

Code: Select all

ISNULL(SUM(CASE Description WHEN 'Data Usage' THEN TotalBytes ELSE NULL END), 0) AS DataUsage

I think ISNULL will probably be more optimised than COALESCE, since it is basically an IF..ELSE, while COALESCE compares more to CASE..WHEN..END.

[GrimStoner]

Can't I just change the CASE to return a 0 if the condition is not met, instead of returning a NULL, and then using ISNULL to change that back to 0?

Like

SUM(CASE Description WHEN 'Data Usage' THEN TotalBytes ELSE 0 END)

[endev8003]

That will work.

And I can't think of anything else to say...

[GrimStoner]

When I try this...

Code: Select all

SELECT 
     CellNumber,
     SUM(CASE Description WHEN 'Data Usage' THEN TotalBytes ELSE 0 END) AS TotalUsage
FROM
     Transactions
GROUP BY
     CellNumber

I get this error :

Code: Select all

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.

Don't understand... I'm not doing any casting anywhere.

[GrimStoner]

Ok... the problem is that the TotalUsage calculated column is too small for the value... How do you specify that the calculated column should be of a specific type? (i.e. bigint)

[GrimStoner]

Nvm...

Code: Select all

SELECT     
     CellNumber,	  
     SUM(ISNULL(CASE Description WHEN 'Data Usage' THEN CAST(TotalBytes AS bigint) ELSE NULL END,0)) AS TotalUsage
                      
FROM
     Transactions
GROUP BY 
     CellNumber

[cbrunsdonza]

I think ISNULL will probably be more optimised than COALESCE, since it is basically an IF..ELSE, while COALESCE compares more to CASE..WHEN..END.

One thing to remember about SQL clients is that they do a pre-optimize on your query before its given to the server. I use COALESCE on a rather large DB (50'000'00 rows x 6 tables = 300'000'000 rows) with joins and it is very efficent. Any loss in performace will be more a result of your TABLE not having the correct INDEXES than your query.

I tend to find that it is better to write SQL than explains your intention than to write it for optimization. So do use ISNULL instead of COALESCE if it makes your query more legible.

[Bladerunner]

EDIT: Sorry, I see endev got you sorted. I'll keep the code here though.

Sample data :

Name | Description | Usage
-------------|--------------|--------
John Smith | SMS |
John Smith | Data usage | 10Mb
John Smith | Data usage | 5Mb
Jane Smith | MMS |
Jane Smith | Data usage | 44Mb

I need a sql query (that I'll run in Access 2007) to give me a SUM of the usage and a COUNT of the SMS's and MMS's for each [Name]. For example, the query will return the following data on the above sample data :

Name | SMS | MMS | TotalUsage
-------------|------|-------|-------------
John Smith | 1 | 0 | 15Mb
Jane Smith | 0 | 1 | 44Mb

I've tried :

SELECT Count(SELECT Description FROM transactions WHERE Description = "SMS"), (SELECT Sum(TotalUsage) FROM transactions WHERE Description = "Data usage"), (SELECT Count(SELECT Description FROM transactions WHERE Description = "SMS")) FROM transactions GROUP BY Name;

but the SELECT Sum(TotalUsage) FROM transactions WHERE Description = "Data usage" part gives me this error :

"At most one record can be returned by this subquery."

Edit : ASCII FTW!

I had a similar project, where I had to get the total transaction amount of each person's account at a shop.
I'm attaching the code here, and hope it will help.

Code: Select all

double dTotal = 0;

            string sCustomer = cboCustomer.Text;
            string sqlGetAmount = "";

            if (sCustomer != "*")
            {
                sqlGetAmount = "SELECT Items.RetailPrice "
                             + "FROM (Transactions INNER JOIN Items ON Transactions.ItemID = Items.ItemID) "
                             + "WHERE Transactions.ClientID = '" + cboCustomer.Text + "' "
                             + "ORDER BY Transactions.TransactionDate";
            }
            else
            {
                sqlGetAmount = "SELECT Items.RetailPrice "
                             + "FROM (Transactions INNER JOIN Items ON Transactions.ItemID = Items.ItemID) "
                             + "ORDER BY Transactions.TransactionDate";
            }
            cnTrans.Open();

            OleDbCommand cmd = new OleDbCommand(sqlGetAmount, cnTrans);
            OleDbDataReader reader = cmd.ExecuteReader();


            while (reader.Read())
            {
                string sAmount = reader[0].ToString();
                dTotal += Convert.ToDouble(sAmount);
            }

            txtAmount.Text = dTotal.ToString("C");

            cnTrans.Close();