Solved

MS Sql Query

Posted on 2009-04-06
5
196 Views
Last Modified: 2012-05-06
I have a table called RestChist and need to pull the following feilds:
CK_CHECKNO, CK_STATUS, CK_COVERS,  CK_TIP,  CK_DISCOUNT,  CK_FOOD,  CK_BEVG,  CK_BEER,  CK_WINE,  CK_LIQUOR, CK_OTHER , CK_TAX  and RESTNO

But, I need to do a distinct count on the CK_CHECKNO to give me a count of how many checks by the server
and
Sum CK_FOOD+CK_BEVG+CK_BEER+CK_WINE+CK_LIQUOR+CK_OTHER as "Items Sold"
and
sum the "items Sold" along with tips, tax minus discounts to get "total"

What I need is to get something like the following

Server         No. of Checks         Items Sold        Tax          Tips       Discounts        Total
1                       10                          $100              $10          $15           $5                  $120
2                        5                           $75                $7.50        $20           $0                 $102.50


Thanks

0
Comment
Question by:huntleyj
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 22

Accepted Solution

by:
8080_Diver earned 500 total points
ID: 24081274
Defining a problem is half of the solution and you have defined it well.
Look at the following and see if it gives you what you want:

SELECT SERVER, 
       COUNT(CK_CHECKNO),
       SUM(CK_FOOD+CK_BEVG+CK_BEER+CK_WINE+CK_LIQUOR+CK_OTHER) AS ItemsSolds,
       SUM(CK_TAX) AS Tax,
       SUM(CK_TIP) AS Tips,
       SUM(CK_DISCOUNT) AS Discounts,
       SUM(CK_FOOD+CK_BEVG+CK_BEER+CK_WINE+CK_LIQUOR+CK_OTHER + CK_TAX + CK_TIP - CK-DISCOUNT) AS Total 
FROM RestChist 
GROUP BY Server

Open in new window

0
 
LVL 3

Author Comment

by:huntleyj
ID: 24081577
Looks like it did the trick. Only I forgot to add that I needed it for a specific date but I should be able to add in that without issue.
0
 
LVL 3

Author Closing Comment

by:huntleyj
ID: 31567211
Worked great
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24081676
If you need it for a specific date, add that as a WHERE clause between lines 8 and 9.  If you need it for more than one date (as in , "by day for a period between mm/dd/yyyy and mm/dd/yyyy", then include the DateClumn in the SELECT right after the SERVER and group by both the SERVER and your datecolumn with the WHERE clause (between the current lines 8 and 9) indicating WHERE yourdatecolumn BETWEEN date1 AND date2.
0
 
LVL 3

Author Comment

by:huntleyj
ID: 24082604
thanks again.
I did the where clause and got the info I think I need. Now I'll take it to the "powers that be" and see if it suits them.
Good to know about the second grouping option incase they want the info that way.
 
Cheers
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question