SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Select statement multiple counts
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Vack
Constraint Violating Yak Guru

USA
497 Posts

Posted - 06/16/2011 :  12:59:44  Show Profile  Reply with Quote
SQL server 2008
Have a table I'm working with called OnTime.

Table looks like the following.


Ord_no    item_no   OnTime   shipped_dt   
 1234      ABC        O      6/30/2011
 1234      CAP        O      6/30/2011
 3456      BBB        L      6/30/2011


Is it possible to write a select statement that will return the total number of records and total number of records where OnTime = O?

Using the above data I would expect.

Total Records     ORecords
     3               2

Edited by - Vack on 06/16/2011 13:00:53

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 06/16/2011 :  14:07:57  Show Profile  Reply with Quote
select
   count(*) as TotalRecords,
   count(case when OnTime='O' then 1 end) as OnRecords
from
   OnTime
Go to Top of Page

jcelko
Esteemed SQL Purist

USA
547 Posts

Posted - 06/16/2011 :  18:59:17  Show Profile  Visit jcelko's Homepage  Reply with Quote
People cannot read your mind, so post your code and clear specs if you really want help. Please post real DDL and not narrative or your own personal programming language. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. Please tell us what SQL product and release you are using. Tell us if you can change the DDL or if you are stuck with it.

We keep asking you to be polite and you never are. DDL? And you clearly have not done any studying. For example “onTime” is an attribute of some event; it is not a valid table name. And you are still writing code with silly assembly language bit flags in SQL! Please start using ISO-8601 dates. And you still do knot know that records are not rows.

Why not do things right instead?

CREATE TABLE Shipment_History
(order_nbr CHAR(4) NOT NULL,
item_nbr CHAR(4) NOT NULL,
promised_shipment_date DATE NOT NULL,
actual_shipment_date DATE --- null means not shipped yet
);

I can then use
CASE WHEN promised_shipment_date <= actual_shipment_date
THEN 'on time' ELSE 'late' END

>> Is it possible to write a select statement that will return the total number of records [sic] and total number of records [sic] where OnTime = O [sic]?

SELECT COUNT(DISTINCT order_nbr) AS order_cnt,
SUM (CASE WHEN promised_shipment_date > actual_shipment_date
THEN 1 ELSE 0 END) AS late_order_cnt
FROM Shipment_History;


--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 06/17/2011 :  10:19:34  Show Profile  Reply with Quote
Small correction to Joe's code:
CASE WHEN promised_shipment_date <= COALESCE(actual_shipment_date, CURRENT_TIMESTAMP)
THEN 'on time' ELSE 'late' END
Go to Top of Page

Vack
Constraint Violating Yak Guru

USA
497 Posts

Posted - 06/17/2011 :  11:01:34  Show Profile  Reply with Quote
jcelko,I don't need your comments anymore. I've had my question answered perfectly both times. Obviously, sunitabeck is a mind reader.

Thanks sunitabeck for your help.
Go to Top of Page

Sean Frost
Starting Member

USA
19 Posts

Posted - 06/18/2011 :  08:32:34  Show Profile  Reply with Quote
JCELKO:

People cannot read your mind, so write your code without using unnecessary abbreviations if you really want to help. order_nbr saves a paltry 3 letters when compared to order_number, but order_nbr is much less readable.

Please do not use version specific dialects in your replies unless you are responding to a question posted in a version specific forum. In Microsoft SQL Server, DATE is a data type that is availabe only on version 10 and above (SQL Server 2008). By using such dialects, you are making your replies practically useless. Use DATETIME instead.

It is not a good idea to put everything in the dbo schema. That is what you are doing when you omit the schema name in your DDL statements. Even if you want to create the relation in dbo schema, be polite and specify the schema name. Instead you are being rude and creating the relation in the dbo schema BY DEFAULT.

When you write code, use proper formatting. The blob you have written is rude and unreadable. We are in 21st century. Don't write SQL code like you were writing COBOL.

We SQL programmers always create a primary key on a relation (except in very very rare circumstances). We do not create "heap tables" if we can avoid it. You are violating one of the fundamental rules and best practices when you create the relation without a primary key. If you would like to learn why primary keys are important, I can expand on it and suggest helpful blogs/websites for you to read and learn.

Unless you plan to record items as being shipped before they are actually shipped, include a check constraint on actual_ship_date column. If you do plan to record items as being shipped before they are shipped, be advised that you would be violating certain provisions of the Sarbanes-Oxley Act of 2002.

In your check constraint, do not use version specific-dialects such as CURRENT_TIMESTAMP. Instead, use GETDATE() function which is available in all versions of Microsoft SQL Server, and even in the old Sybase versions.

As someone who writes SQL code for a living, my advice to you is to stop advising and continue with book-writing.

Let me start over and try to correct your rudeness and fundamentally wrong narrative:

CREATE TABLE dbo.Shipment_History
(
     order_number        char(16) NOT NULL,
     item_number         char(16) NOT NULL,
     promised_ship_date  DATETIME NOT NULL,
     actual_ship_date    DATETIME NULL
          CHECK (actual_ship_date <= getdate())
     
     primary key (order_number asc, item_number asc)
);


Compare this with the rude code you posted. Be polite and study the advice I have given above before posting any responses again.

I could go on and on but I won't. However, I want to add just one more thing. We are SQL programmers; we do not use layman's terms such as "tables", "rows", "columns" etc. A relational database is not a furniture store with tables and chairs. In the name of Codd, please use the correct terminology. What you rudely referred to as a table is really a relation. There are no such things as rows. They are either tuples or records. And a relation does not have columns, it has attributes, sometimes also called fields!!

Edited by - Sean Frost on 06/19/2011 20:28:16
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000