Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 204
  • Last Modified:

MS Sql Query

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
huntleyj
Asked:
huntleyj
  • 3
  • 2
1 Solution
 
8080_DiverCommented:
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
 
huntleyjAuthor Commented:
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
 
huntleyjAuthor Commented:
Worked great
0
 
8080_DiverCommented:
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
 
huntleyjAuthor Commented:
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now