Solved

MS Sql Query

Posted on 2009-04-06
5
201 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

How to use Variables  and Custom code in SSRS report and Assembly reference to use compile shared code in SSRS. Its big question for all who are working with SSRS. It is easy to create assembly and refer in SSRS report, still there are some steps…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

724 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