Select statement multiple counts

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

General SQL Server Forums

New to SQL Server Programming

Select statement multiple counts

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2011-06-16 : 12:59:44

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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-16 : 14:07:57

[code]select
count(*) as TotalRecords,
count(case when OnTime='O' then 1 end) as OnRecords
from
OnTime[/code]

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-06-16 : 18:59:17

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-06-17 : 10:19:34

Small correction to Joe's code:

CASE WHEN promised_shipment_date <= COALESCE(actual_shipment_date, CURRENT_TIMESTAMP)
THEN 'on time' ELSE 'late' END

Vack
Aged Yak Warrior

530 Posts

Posted - 2011-06-17 : 11:01:34

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.

Sean Frost
Starting Member

19 Posts

Posted - 2011-06-18 : 08:32:34

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!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources